Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 991
mbald23 Avatar asked Mar 16 '17 12:03

mbald23


People also ask

How do you auto save and exit Excel without macro?

Enable the workbook you want to automatically save and close after inactivity for a certain seconds, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window. 5. Then after 15 seconds, there is a dialog popping out for remind you saving the workbook, and click Yes to save and close the workbook.

How do I save a workbook as a copy?

Select File > Save As > Download a Copy. If Excel asks whether to open or save the workbook, select Save. Note: If you select Open instead of Save, the workbook will open in Protected View.


Video Answer


1 Answers

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.

like image 175
Srijan Avatar answered Oct 19 '22 16:10

Srijan