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!

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.