Right now, the VBA Macro works great on 'my' PC be/c I have it saving as a specific file to my download folder. I need this to work on anyone else's PC that runs this Macro and have the same function. I tried to google, and I am not finding exactly what I need.

Below is the code I am using:

Sub UnLoad_UnPick()
'
' UnLoad_UnPick Macro
'

'
Range("Q:Q").Style = "CURRENCY"


Columns("O:O").Select
    Selection.Cut

Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

Range("A1").Select

Application.DisplayAlerts = False
     ChDir "C:\\users\"1st name"."last name"\downloads"
     ActiveWorkbook.SaveAs Filename:= _
          "C:\users\"1st name"."last name"\downloads\Transaction_Details.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
'
End Sub

Right now, the VBA Macro works great on 'my' PC be/c I have it saving as a specific file to my download folder. I need this to work on anyone else's PC that runs this Macro and have the same function. I tried to google, and I am not finding exactly what I need.

Below is the code I am using:

Sub UnLoad_UnPick()
'
' UnLoad_UnPick Macro
'

'
Range("Q:Q").Style = "CURRENCY"


Columns("O:O").Select
    Selection.Cut

Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

Range("A1").Select

Application.DisplayAlerts = False
     ChDir "C:\\users\"1st name"."last name"\downloads"
     ActiveWorkbook.SaveAs Filename:= _
          "C:\users\"1st name"."last name"\downloads\Transaction_Details.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
'
End Sub

• excel
• vba
• save-as

• 4 stackoverflow/questions/23070299/… – BigBen Commented Nov 18, 2024 at 19:44
• 1 You don't need ChDir (which can anyway be unreliable if users have multiple drives) if you're supplying the full path to SaveAs – Tim Williams Commented Nov 18, 2024 at 20:21

1 Answer 1

There is a number of ways in which your code could be improved, some of them are:

• Error Handling - Added an On Error GoTo ErrorHandler block to handle any runtime errors gracefully.
• Removed unnecessary Select statements to make the code cleaner and faster.
• Added a message box to inform the user where the file has been saved.
• Format only relevant cells in Column Q instead of the entire column:

Sub UnLoad_UnPick()

    Dim lastRow As Long
    Dim downloadsPath As String
    ' Get the current user's Downloads folder path
    ' (or use another method as suggested above)
    downloadsPath = Environ("USERPROFILE") & "\Downloads"

    On Error GoTo ErrorHandler

    ' Format only the relevant cells in column Q
    lastRow = Cells(Rows.Count, "Q").End(xlUp).Row
    If lastRow > 1 Then
        Range("Q2:Q" & lastRow).Style = "Currency"
    End If

    Columns("O:O").Cut
    Columns("A:A").Insert Shift:=xlToRight
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=downloadsPath & "\Transaction_Details.xlsx", _
                          FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.DisplayAlerts = True

    MsgBox "The file has been saved successfully in: " & downloadsPath, vbInformation, "Save Successful"

    Exit Sub

ErrorHandler:
   
    Application.DisplayAlerts = True
    MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
End Sub