Active Workbook

[Kim]

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you

 

[Luke M]

Correct, when you save-as, you are changing the name of active file, not
creating a copy. Perhaps this code?

Sub ReName()
xName = ActiveWorkbook.FullName
'Change this to something appropriate
ThisWorkbook.SaveAs "New Name"
'Opens original file
Workbooks.Open (xName)
'Close the copied file
ThisWorkbook.Close
End Sub

 

[Dave Peterson]

Look at SaveCopyAs in VBA's help.

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you

 

[Gary Brown]

You need to re-open the original.
Prior to save as, put the current name in a variable.
After the save as, open the original using that variable.

Something like...

strOriginalName = ActiveWorkbook.FullName
....save as code
Workbooks.Open Filename:= strOriginalName

 

[Harald Staff]

SaveCopyAs

HTH. Best wishes Harald

 

[Kim]

I have another kink in this.
I am working in 2007, but original file was .xls and saved as .xls. With
the SaveAs I originally had FileFormat:=xlNormal, but this returned "argument
not found".

 

[Dave Peterson]

SaveCopyAs doesn't give you a choice of what type of file you're saving as.
It's saving as an exact copy of the workbook file you're saving.

If you need to change fileformat, then .savecopyas isn't going to do it by
itself.

You could:
savecopyas
open that copy you just saved.
save it in the format you want.
close that file.

And the original workbook stays untouched.

 

[Kim]

Actually, it is the same format that it started in, so I don't know what the
problem is.

 

[Dave Peterson]

Don't use the fileformat parm in your .savecopyas line.

If that doesn't help, then you'll have to share your code.