Troubleshooting macro to save in Compatability Mode

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

Have the following code to save the active sheet into a new
workbook.
How can I specify that it is to be saved in the 97-2003 compatibility
mode, and in a macro free workbook?

Sub Test()
'
' Test Macro
'
ActiveSheet.Copy
Set newbk = ActiveWorkbook
newbk.ActiveSheet.Cells.Copy
newbk.ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

InitialName = ("Monthly_Inventory") & ".xls"
FName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:="Excel Files (*.xls), *.xls")
newbk.SaveAs Filename:=FName

'
End Sub

Thanks for your thoughts.
Pierre
 
I figured it out. If it was generated in compatibility mode, it will
save as the same.
If the macro that created it is located on another worksheet in the
original file, the sheet thats copied and saved in the new workbook
will not contain any macros.

Thanks for any effort expended.
Pierre
 
I figured it out. If it was generated in compatibility mode, it will
save as the same.
If the macro that created it is located on another worksheet in the
original file, the sheet thats copied and saved in the new workbook
will not contain any macros.

Thanks for any effort expended.
Pierre
Click to expand...

Note that once you save a 2k7 file in 2k3 compatibility mode, you MUST
close, then re-open the file to have it be in effect. Otherwise, you
will get cut and paste errors, etc.
 
Back
Top