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.

Please Login or Register  to view this content.

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

Sincerely,
Leith Ross