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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. MacOS
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

VBA Code:
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top