Macro Help - Sheet Copy and Save as with a specific File Name

[Johnny Thunder]

Hello All,

I have been working with VBA on Mac for about 2yrs now and I just noticed in the last few weeks that code that I had written on several files in the past no longer works and I am not entirely sure why.

The code is listed below

Sub Create_Upload()

Dim RelativePath As String, DT As String, WbNam As String, ws1 As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
        
Set ws1 = Sheets("Actuals Data")
        
    RelativePath = ThisWorkbook.Path & "/"
        DT = Format(CStr(Now), "mm_dd_yyyy hh.mmam/pm")
            WbNam = "Submission Template " & " - " & DT    'Creates the File Name
            
            ws1.Copy    'Copies the entire sheet into a new workbook
                ActiveWorkbook.SaveAs fileName:=RelativePath & WbNam & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
                    ActiveWorkbook.Close Save


MsgBox "Submission file saved to the folder where this tool resides."

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

I can confirm that all the qualifiers like the ws1 sheet exist and are spelled correctly. The error I am currently getting is a Error 1004 "Method Saveas of object workbook failed" and it is erroring out on my SaveAs line.

Any help is appreciated in either troubleshooting or rewriting my script.

Thanks in advance

 

[Joe4]

It looks to me like you are trying to save the Active Workbook (the one with this VBA code) as an "xlsx" file.
That is not allowed. You cannot save a file with VBA code to an "xlsx" file format.
You must choose one of the formats that allow VBA, i.e. "xlsm", "xlsb", etc.

 

[Johnny Thunder]

The original intent for the code was to save a copy of just the WS1 sheet and make that standalone sheet a .xlsx file. If that is not what the code does then maybe that is why it is not working. Any ideas on a revision? Thanks for chiming in by the way. You have helped me several times in the past.

 

[Joe4]

When you look in the VBA Editor, when you look at the "Actuals Data" sheet module, is there any VBA code in there?
Your VBA code should work, as long as their is no VBA code in the "Actuals Data" sheet module in the VB Editor (so any VBA code in your underlying file must be in OTHER modules).

 

[Johnny Thunder]

When you look in the VBA Editor, when you look at the "Actuals Data" sheet module, is there any VBA code in there?
Your VBA code should work, as long as their is no VBA code in the "Actuals Data" sheet module in the VB Editor (so any VBA code in your underlying file must be in OTHER modules).

Just checked, there is no code in any sheet other than the Module to create this copy that was posted here.

 

[Johnny Thunder]

I was doing some testing and just noticed that the file seems to work without any issues if I save it to my desktop but when it is stored within our companies Box Accounting folders is when I get this weird error. It has always been stored in these folders and I have several other files that have all worked in the past with almost the exact same script just different file names and outputs.

 

[Joe4]

I was doing some testing and just noticed that the file seems to work without any issues if I save it to my desktop but when it is stored within our companies Box Accounting folders is when I get this weird error. It has always been stored in these folders and I have several other files that have all worked in the past with almost the exact same script just different file names and outputs.

Hmmm... That is odd. Not sure what to make of that.