[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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.
0
asmyatt
Asked:
asmyatt
  • 2
1 Solution
 
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
 
Randy DownsOWNERCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now