VBA script to copy of active workbook and add a timestamp (mm_dd_yyy) and place copied file in a specific folder

[J7House1984]

Hello,

I am a beginner at VBA, I know enough to get myself stuck frequently. I am trying to write a VBA script, for archiving purposes, to write a copy of file and add a timestamp in this format (mm_dd_yyy) and place the copied file in a specific folder.

The location folder path is H:\fbqf9009\Documents\Campaigns\Campaign Reporting.

The end result would ideally be Filename mm_dd_yyy.xls


The script I have thus far is and I getting error messages. I know this is isn't everything I am trying to do in the script but I am stuck and need help.

Sub SaveArchive()
Dim dt As String
WbName As String

WbName = "ActiveWorkbook.name"
dt = Format(CStr(Now), "mm_dd_yyyy")
ActiveWorkbook.SaveAs Filename:=WbName & dt
End Sub

Please help?!?

 

[bbotzong]

Hi,

You're close, but you missed a few things.

First, you MUST put a DIM statement in front of all variables you declare. You forgot it for WBName in line 3.

Here's a macro that should work for you which will save the open workbook in the current folder.

Sub SaveArchive()
Dim dt As String
Dim WBName As String

WBName = ActiveWorkbook.Name & " " & Format(Date, "mm-dd-yy")
ActiveWorkbook.SaveAs Filename:=WBName

End Sub

Hope this helps.

Bill B.