VBA to name the active workbook and save it to the desktop of different users computers

[parkerbelt]

I have a spreadsheet that has a value in a cell that I'm calling WMWeek. The value will change, but is currently 201736

I'm trying to save the active spreadsheet with the following file name to the desktop of the person who is running the program: Weekly Report - 201736.xlsx .

I'm using the following code, but when I run it, I'm getting Run-time error '1004' The specified dimension is not valid for the current chart type at the line ActiveWorkbook.SaveAs emailReport:

WMWeek = Cells(Cells(Rows.Count, "A").End(xlUp).Row, 1).Value
emailReport = Environ("HomeDrive") & Environ("HomePath") & "\Desktop\Weekly Report - " & WMWeek & ".xlsx"
ActiveWorkbook.SaveAs emailReport

 

[parkerbelt]

I found a solution:

Dim emailReport As String
emailReport = Environ("USERPROFILE") & "\Desktop" & "Weekly Report - " & WMWeek & ".xlsx"
ActiveWorkbook.SaveAs Filename:=emailReport, FileFormat:=xlTextPrinter, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False