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.

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. — Pᴇʜ
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. — mbald23
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. — Pᴇʜ

Srijan Srijan · Accepted Answer · 2017-03-16T13:13:20

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.

Save a copy of an excel workbook without macro

1 Answers