Macro that Selects Specific Excel Worksheets to a PDF file & Saves to the User's Desktop

I would for the following code to be associated with a button (I know how to assign macros to buttons). When the user selects the button, the macro will perform the following:

1.) Select the worksheets titled "Cover Page", "Authorization for Cap Project", "Financial Inputs" and "Capital Budgets";
2.) Saved the selected worksheets as a pdf on the user's desktop with the file name " ' Project_Name' - CAR - 'TodayDate' ". "Project Name" and "TodayDate" are named cells within the workbook.
3.) The pdf version of the the selected worksheets will be displayed.
4.) A message box will appear that states, "The displaying pdf file has been saved on your desktop under the name "' Project_Name' - CAR - 'TodayDate'". Please keep this file name for all future correspondence."
- Remember "Project_Name" & "TodayDate" have are named cells in the workbook
5.) Return to the original worksheet where the button is located.

Sub SaveAsPDF()

Dim OriginalSheet As String

OriginalSheet = ActiveSheet.Name

Sheets(Array("Cover Page", "Authorization for Cap Project", "Financial Inputs", _
"Capital Budget")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"C:\Desktop" & Range("Project_Name").Value & " - CAR - " & Range("TodayDate").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Sheets(OriginalSheet).Select

MsgBox "The displaying pdf file has been saved to your Desktop under the name " & Range("Project_Name").Value & " - CAR - " & Range("TodayDate").Value & ".pdf" & _
" Please keep this file name for all future correspondence"

End Sub


Thank you so much Excel/VBA Gurus and to the creators of this site. ExcelForum is life(job)-saving!

Thank you for your prompt response; however, I'm receiving Error 400 now. Doesn't that error have to do with the array? Thanks again!

Thanks for the note, Jeffrey.

Below is my code in the proper format:

Please Login or Register  to view this content.