MrExcel Publishing
Your One Stop for Excel Tips & Solutions

What macro commands can be used to take a daily workbook file that has a new name each day, copy the


Posted by Mike on September 13, 2001 10:18 AM

Repost from 9-10, hoping will spot the repost who might be able to help.

What macro commands can be used to take a daily workbook file that has a new name each day, copy the worksheet (new name each day) and then rename the copy. There is only one worksheet in the new file each day when it is downloaded from our application.
(Note, it is possible that this macro will be used on files that do have the same name each day, but I'd like to macro to be usable with other files, with identical column layouts, but completely different file names when they are generated by our application.)

Thanks for any help or referral to resources or books that will be helpful.

P.S. I've set up a macro that creates adds a new worksheet and then copy & paste from the original one (which I rename Sheet1) which works ok but I thought there may be a better approach/solution.


Posted by Mike on September 13, 2001 10:21 AM

Re: What macro commands can be used

Sorry about the long Subject Name, still getting the hang of posting here; couldn't find a way to delete my error.

Posted by Barrie Davidson on September 13, 2001 10:49 AM

Mike, try this

Sub File_Save_As()
' Written by Barrie Davidson
Dim fileName As String

fileName = Application.GetOpenFilename
Workbooks.Open fileName:=fileName
MsgBox ("Enter SaveAs file name")
fileName = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls),*.xls")
ActiveWorkbook.SaveAs (fileName)
End Sub

Hope this is what you need.

Regards,
Barrie
Barrie Davidson