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:
Sub Temp1()
'
' Temp1 Macro
'
'
Sheets("Match1 Values Only").Select
Sheets("Match1 Values Only").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\barsc\Documents\Barry's Documents\Table Tennis\Ishaka Table Tennis\League Test Level\xxxxxxxxxx_date_yyyy.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
' Windows("MatchSheets).xlsm").Activate
End Sub
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:
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\barsc\Documents\Barry's Documents\Table Tennis\Ishaka Table Tennis\League Test Level\Z-" & MatchPlayedDate & "_" & MatchSchedDate & "-" & MatchPlayedDateKey & "-MatchResults.xlsm" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Windows("MatchSheets.xlsm").Activate
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.
Bookmarks