Search code examples
excelexcel-2010vba

Save a copy of an excel workbook without macro

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.

Solution

  • 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.