VBA - copying existing file and renaming with date

I am new to VBA and trying to create a macro that will ask for a date using an input box (format mm/dd/yy), then make a copy of the last file used in the folder since separate files are created for each month, and renaming the file using the date that was entered in the input box with additional text of what the file is (ex. 03.31.07 Excel file.xls) I have the code for the input box but can't figure out the rest. Can anyone help me??

Thanks!

Hello Firefly,

What do you mean by last file? Last file accessed or the last physical file in the folder?

Sincerely,
Leith Ross

By the last file, I mean the last monthly file that was created. I have a different file for each month...

01.31.07 Excel File.xls
02.28.07 Excel File.xls
03.31.07 Excel File.xls

I want to copy or open the 03.31.07 Excel File.xls and rename it 04.30.07 Excel File.xls using the date entered into the input box since each month I need to use the last month's file and update with current information.

Hello Firefly,

After you get your date from the InputBox, call this macro. It will open the Open File dialog using the folder path you provide. The user can then select the file to be copied and renamed.

Sub CopyRenameFile(Search_Folder As String, NewFileName As String)

  Dim OldDir As String
  Dim FSO As Object
  Dim NewFileName As String
  Dim OldFileName As String
  

    OldDir = CurDir()
    ChDir (Search_Folder)
    
    OldFileName = Application.GetOpenFilename("Excel Files, *.xls;*.xla;*.xlt")
      If OldFileName = "" Then Exit Sub
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.CopyFile OldFileName, NewFileName

    ChDir (OldDir)
    
End Sub

Example:
CopyRenameFile "C;\My Documents\Excel Files", "04-23-2007 Excel File.xls"

Sincerely,
Leith Ross