Save a copy of an excel workbook without macro

Question

I have an Excel 2010 template file with macros that includes the following code:

    ActiveWorkbook.SaveAs Filename:= _
    newname, FileFormat:= _
    51, CreateBackup:=False

This saves the current workbook as a non-Macro enabled workbook, but then I obviously cannot run the rest of the macros that I need.

I tried to use

    ActiveWorkbook.SaveCopyAs Filename:= _
    newname, FileFormat:= _
    51, CreateBackup:=False

This yields a syntax error. My goal is to save a copy with the new name, so the template file remains unchanged and can be run daily.

Billiot

Why do you save it as a non-macro enabled workook if you need macros? That doesn't make sense at all. And SaveAs indeed does save a copy the original template file still remains. – Screwball

The next part of the macro takes the new workbook and e-mails it out to a specific mailing list. If it saves as a non-macro enabled workbook, that part will not run. – Billiot

This won't work this way. You would need a helper workbook to solve this. Lets say you have workbook A which has macros. This workbook A opens your template B and saves it as B-copy (without macros) and then emails out B-copy. This would work. – Screwball

Answer 1

try this:

    Dim wMacro As Workbook     'workbook you want to save

    wMacro.Sheets(Array("Sheet1", "Sheet2", "etc")).Select
    wMacro.Sheets(Array("Sheet1", "Sheet2", "etc")).Copy

    ActiveWorkbook.SaveAs Filename:= "filename.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

it will create a copy and save.

Ericson

Answer 2

The accepted answer will throw an error, since new_Workbooks object is not set, here un updated and revised code with some optional:

Sub Workbook_Copy_and_Save()

    Dim new_Workbooks As Workbook

    ' Create a new workbook
    Set new_Workbooks = Workbooks.Add 
    
    ' Copy the specified sheets to the new workbook
    ThisWorkbook.Sheets(Array("SheetA", "SheetB", "etc")).Copy Before:=new_Workbooks.Sheets(1) 
    
    ' Optional disable alerts of next action
    Application.DisplayAlerts = False

    ' Optional delete Excel default Sheet1 in the new file
    new_Workbooks.Sheets("Sheet1").Delete

    ' Save the copy in the same directory of the origina file, named as newFileName.xlsx
    new_Workbooks.SaveAs filename:=ThisWorkbook.path & "\" & "newFileName.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    ' Optional close the new file created
    new_Workbooks.Close

    ' Optional re-enable alerts if more code needs to be run
    Application.DisplayAlerts = True

End Sub

Pino