Excel VBA macro works flawlessly on my computer, but has different errors on different machines?

Question

I'm working on a project which works without macros so far. When implementing macros, my machine executes them without errors, but when testing it on other machines for other users, they have issues at what seem like random points in the code itself - but always stopping at the "save this document" portion. I have elongated the line pertaining to saving it, which gave me a better way to name the file, but cannot figure out why other computers would have issues running these macros when mine does not.

The below code is heavily ripped and cobbled together, but should create a file on the user's desktop, regardless of the username, defeating an original issue I had with multiple users having different save paths. It seems to work fine up to the portion involving actually saving the document - i.e., it creates a new worksheet with the two sheets I need, formatting and all, but throws a 1004 error and does not save/name the document. Any assistance is appreciated, I'm relatively new to using macros in excel.

Sub XWorkbook()
    Dim SavePath As String
    Dim File1 As String
    Dim Filename As String
    
    Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\[MyFolderName]\"
                             
    If Dir(Path, vbDirectory) = "" Then MkDir Path

    Application.DisplayAlerts = False

    Worksheets(Array("[Sheet1]", "[Sheet2]")).Copy
    With ActiveWorkbook

    SavePath = "C:\Users\" & Environ("UserName") & "\Desktop\[MyFolderName]\[DocumentName]"
    File1 = " "
    Filename = File1 & " " & Format(Now(), "MM-DD-YYYY") & ".xlsm"

    ActiveWorkbook.SaveAs SavePath & Filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    End With
End Sub

I have attempted to elongate the portion responsible for saving the document using the environ() function as well as adding a portion to create a folder so that each user should* have a standardized folder on their desktop to eliminate any problems with specific users having different paths. Beyond that, I am clueless. Running the latest office 365, as are all other users. The error always points exactly here when debugging:

ActiveWorkbook.SaveAs SavePath & Filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

I have also ensured that all users save the origin file locally, and enable macros when opening the document. These are all the exact same model of dell thinkpads and should be similar in almost every way, barring user information, which I thought that environ() would assist with. I have seen some similar questions talk about drive mapping, but to my knowledge, that would already be accounted for, and no one should have settings which would replace the date "MM-DD-YYYY" hypens with slashes. Any ideas?

Answer 1

This works for me:

Sub XWorkbook()
    
    Dim wbPath As String, saveName As String
    
    wbPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\MyFolderName\"
    If Dir(wbPath, vbDirectory) = "" Then MkDir wbPath
    
    saveName = wbPath & "DocumentName " & Format(Now(), "MM-DD-YYYY") & ".xlsm"
    
    'assuming the sheets are in the same workbook as this code...
    ThisWorkbook.Worksheets(Array("Sheet1", "Sheet2")).Copy
    'save the new workbook with the sheet copies
    ActiveWorkbook.SaveAs Filename:=saveName, _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub