Macro Save As

J

John

I created a macro to save a file, but I want to be able to archive it in a
folder and change the name each time. As I have it noe it replaces the
existing file. How can I get the dialog window to appear to Save As a new
file?
 
C

CLR

This is what I use.....it's set as generic and must be tweaked for each
application.

Sub SaveArchive()
' Saves the workbook to a predetermined Archive
'Directory and appends date and time to filename,
' then re-configures file so it will naturally be saved to
'the directory from whence it came.
CurrentPath = CurDir
ArchivePath = "C:\SpecifyYourPathHere"
WorkBookName = ActiveWorkbook.Name
Fname = ArchivePath
Fname = Fname + Worksheets("YourSheetName").Range
_("YourSelectedCell").Value 'Used to name archived file to cell value
Fname = Fname & Format(Time, "_hh_mm_ss") & Format _(Date,
"_Mmm_dd_yyyy")
Range("a8").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Fname
Fname = CurrentPath + "\" + WorkBookName
ActiveWorkbook.SaveAs Fname
Application.DisplayAlerts = True
End Sub

hth
Vaya con Dios,
Chuck, CABGx3
 
Top