Save a copy 'without VBA modules'

[DonAndress]

Hello.

I have a macro that contains multiple modules and lots of code lines and also many worksheets. Using VBA I'd like to create a copy of this file but I'd like to strip it completely off the code (modules) and also I'd like to save only 3 specific sheets out of all existing. This is to save drive space as this kind of archiving will be performed on a daily basis.

Right now I have:
ActiveWorkbook.SaveCopyAs Filename:=Path & FileName & ".xlsm"

but of course it saves an exact copy of the original file.


How can achieve all above?
Simple ".xls" at the end gives a corrupt file as an output.

 

[pgc01]

Hi

".xlsm" can have macros, the m is for "macro enabled"

To save without macros use ".xlsx"

 

[DonAndress]

Ok and what about sheets?
How do I save only desired sheets?

 

[DonAndress]

Well actually I changed the extension to xlsx and I still get the error.
It says " Excel cannot open the file [...] because the file format or file extension is not valid."

 

[pgc01]

Hi

The extension ".xlsx" it the one for the spreadsheets without macros.

To save some sheets in a new workbook without macros I'd:

- copy the sheets to a new workbook
- save the new workbook as a workbook without macros

This is a simple example:

This code copies the sheets Sheet1, Sheet2 and Sheet4 from the active workbook to a new workbook, saves the new workbook as a workbook without macros and closes it.

Sub Test()

    ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet4")).Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="c:\tmp\test.xlsx", FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
End Sub

Please test.

 

[DonAndress]

Works perfectly. Thank you!

 

[pgc01]

You're welcome. Thanks for the feedback.