Getting error when saving a worksheet to a new workbook in a macro

[pongmeister]

I am trying to save a worksheet from one workbook as a new workbook with a variable name. I've found the code works fine if I use a static name. For example in this macro:

Please Login or Register  to view this content.

The static workbook "xxxxxxxxxx_date_yyyy.xlsm" is saved in my directory however in my macro (StoreMatchSheets) in workbook (MatchSheets.xlsm) I get an error box.

The code in the macro is:

Please Login or Register  to view this content.

I get the following popup box:

The following feature cannot be saved in macro free workbooks:

. VB Project

To save a file with these features, click no, and the choose a macro enabled file type in the file type list

To continue saving a macro free workbook, click yes.

When I click no the result is error msg: Runtime error 1004 VB projects & slm sheets cannot be saved in a macro free workbook

When I click yes the result is error msg: Runtime error 1004. This extension cannot be used with the selected file type. Change the file extension in the file name test box or select a different file type by changing the save as type.

Note: I have tried changing file name extension to xlsx, xlsm and xlsb.


I'm attaching the main workbook and three other workbooks which may have links from formulas in the main book (MatchSheets.xlsm).

The variable name in macro (StoreMatchSheets) looks correct to me when I hover the cursor over the variable strings.


Thanks for any advice.

[Melvosh]

My initial guess is that you need to change FileFormat:=xlOpenXMLWorkbook to FileFormat:=xlOpenXMLWorkbookMacroEnabled. Unfortunately, I can't test VBA right now.

[pongmeister]

Thanks so much Melvosh - that fixed the problem immediately.