save a copy

[Opal]

In Excel 2003, is it possible to create a
macro that will save a copy of the workbook
in a specific location, allow the user to name
it but keep the original workbook open?

 

[Opal]

After a little more research, I tried this:

Sub Saveit()

Dim strName As String
Dim sDir As String

sDir = "C:\Temp"
ChDir (sDir)

strName = ActiveWorkbook.Worksheets("Main").Range("G1") & ".xls"

ActiveWorkbook.SaveCopyAs Filename:=strName

End Sub

But it doesn't work....can anyone help me out? Thanks.

 

[Francis]

In Excel 2003, is it possible to create a
macro that will save a copy of the workbook
in a specific location, allow the user to name
it but keep the original workbook open?

Here's a macro I created many years ago. You may want to tweak it a
little because I just used it on my own computer. For example, if you
want others to use it you may want to have the macro create the folder
if it doesn't exist.

Sub BackupCurrentWorkbook()
Dim origName As String
Dim cpyFname As String
Dim newName As String
Dim dt As String
dt = Format(Now(), "dd-mmm-yy hh:mm am/pm")
origName = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) -
4)
cpyFname = InputBox("This will save a backup copy of the current
workbook in the C:\Backup\ folder." & Chr(10) & Chr(10) & "Enter the
filename you wish to use for the backup copy.", "Save Backup Copy",
origName)
If cpyFname = "" Then Exit Sub
dt = Format(Now(), "yymmddhhmm")
newName = "C:\Backup\" & cpyFname & " " & dt & ".xls"
ActiveWorkbook.SaveCopyAs (newName)
On Error GoTo errLine:
Exit Sub
errLine:
MsgBox "Sorry! An error occured and the file could not be backed
up!", vbOKOnly, "Error !"
End Sub

Regards,
Francis Hayes
www.TheExcelAddict.com

 

[Opal]

Thank you! Got it what I wanted!!

Dim cpyName As String
Dim newName As String

cpyName = ActiveWorkbook.Worksheets("Main").Range("G1")
newName = "C:\Temp\" & cpyName & ".xls"

ActiveWorkbook.SaveCopyAs (newName)