Macro for saving a worksheet to desktop by any user of the file

Hi,

I have created a file that outputs a letter for a client.
I then created a Macro that moves and copies the worksheet to a new Excel file and saves to my desktop and names the worksheet "Customer Copy" in excel.
I also save this copied file as a PDF file same name "Customer copy".

The macro works fine on my PC, the trouble I have is when I send this file to others users they get an error message when they try to run the Macro on their PC.
I know the reason is that my Macro is linked to my user name (Hardeep.Sidhu)

Below is my Macro:

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets("Output Summary").Select
Sheets("Output Summary").Copy
ChDir "C:\Users\hardeep.sidhu\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\hardeep.sidhu\Desktop\Customercopy.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\hardeep.sidhu\Desktop\Customercopy.pdf", Quality:=xlQualityStandard _
, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveWindow.Close
End Sub



The error users get in that macro is the line below:

ChDir "C:\Users\hardeep.sidhu\Desktop"

This links to my desktop, How can I change it so that it automatically go to the relevant users desktop?

Please can anyone correct my Macro above, I am a novice at this stuff and have struggled for the last 7 hours.

Thanks

Hardeep

Hi Alan,

It is now not saving and I get the error message end/debug on the debugger the line:
ChDir "C:\Users\Environ(Username)\Desktop"

Is higlighted yellow.

Hi Khaled,

That is confusing where would I insert that in my original macro.

Regards

Hardeep

When I do that is says
Compile error: Expected end of statement

Originally Posted by Kelshaer

Here you Go.

Please Login or Register  to view this content.

Hello

Sorry for necro-ing an old thread, but this appears to be exactly what I need!

However, I am trying to change the name in this macro from "Customercopy" to whatever is present in my H1 cell. I can't seem to figure this out...

So far, I have been using this simple macro for my day to day needs, but this won't work if I send it to a different pc:

Sub SaveByCell()
Dim sFileName As String
sFileName = Sheets("Sheet1").Range("H1")
If sFileName = "" Then Exit Sub
ThisWorkbook.SaveAs "E:\Users\MyUserName\Desktop\Autosaves\" & sFileName
End Sub

Any ideas? Thanks!