Workbook.SaveAs won't save without the VBA Code

Question

I have a macro-enabled workbook that I have to send out to others via email after the code is run. The problem is with the macros the workbook is to large to send. I have been manually saving a copy of the workbook as .xlsx. I'm now trying to do this automatically.

My code to save is as follows:

Application.DisplayAlerts = False

ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:="Workbook_No_Macros" 
FileFormat=xlWorkbookNormal

Application.DisplayAlerts = True

The problem is when I go and look at the one that is supposed to not have the VBA code is still too large because the code is being saved with the workbook even though it is being saved a workbook that is not macro-enabled. This also results in Excel seeing the file as corrupted and not wanting to open it outside of compatibility mode.

Any ideas on how to save the workbook without the code embedded in it?

Edit: I have also tried

FileFormat:=56

Tehscript Tehscript · Accepted Answer · 2017-08-01T13:40:05

You can save a copy of it. Code taken from here:

Sub M_snb()
    Application.DisplayAlerts = False

    ThisWorkbook.Sheets.Copy
    With Workbooks(Workbooks.Count)
        .SaveAs ThisWorkbook.Path & "\copy_001.xlsx", 51
        .Close
    End With

    Application.DisplayAlerts = True
End Sub

Workbook.SaveAs won't save without the VBA Code

2 Answers