Excel Macro to copy and rename when workbook is saved

Experts,

I need a macro that will copy the opened workbook to a destination saved as the same filename & datetime. I need the macro to generate as soon as the workbook is saved. Any ideas?

Thanks.
LVL 2
asmyattAsked:
Who is Participating?
 
Randy DownsConnect With a Mentor OWNERCommented:
Try this. You may need to close your file before copying though.

http://www.dbforums.com/microsoft-excel/1609243-how-copy-file-excel-vba.html

Dim SrceFile
Dim DestFile

SrceFile = "g:\jerry\export.zip"
DestFile = "c:\data\zippedfiles\export.zip"
FileCopy SrceFile, DestFile
0
 
Randy DownsOWNERCommented:
Maybe this will help

http://www.ozgrid.com/forum/showthread.php?t=61914&page=1


... simply copying your master/template sheet (it looks like you are copying everything). This is equivilent to EDIT > "Move or Copy Sheet" > checking the "Create a copy" box



VB:
Sub CopySheet()
    Dim MySheetName As String
    MySheetName = "TestSheet"
    Sheets("MasterSheet").Copy After:=Sheets("MasterSheet")
    ActiveSheet.Name = MySheetName
End Sub
0
 
asmyattAuthor Commented:
I need the macro to save the entire excel file to another excel file.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.