Selected Answer
Hi MrGerlach50 and welcome to the Forum.
You can attach an Excel file to your question (which nearly always helps us 9 but you didn't so I'll make some guesses...
You're referring to the file from Don's tutorial Create PDF's of Any Worksheet with a Single Button Click.
Provided your file path "C:\Users\Steven\Desktop\Performance Dashboards\" actually exists, it seems fine. The second part of variable newFilename is:
ActiveSheet.Range("A1").Value & ".pdf"
so the possible cause of error 1004 is that the active sheet (the one you can see in a single sheet view):
- is the wrong one and cell A1 is empty OR
- the contents of cell A1 start or end with a space, period, hyphen or underline.
- A1 contains one or more of the characters which are not permitted in filenames by Windows.
The latter "illegal" characters are as follows:
- * asterisk
- ? question mark
- / forward slash
- $ dollar sign
- ! exclamation point
- ' single quotes
- " double quotes
- : colon
- @ at sign
- + plus sign
- ` backtick
- | pipe
- = equal sign
Suggest you check for those and try again.
Also it's good practice too (but not always necessary) to keep your filenames to a reasonable length, under 31 characters say.
REVISION 18 November 2023:
In the file now attached to your question, the file path and name is created by the line
' Make a filename.
newFilename = "C:\Users\Steven\Desktop\" & _
ActiveSheet.Range("E2").Value & ".pdf"
but E2 contained:
Last Update 11/16/2023
which contains two instances of the (illegal) forward slash /.(see above)
The simplest way is to rewrite the date in a different, (filename) legal format. This is shown in the attached, revised file where (yellow) cell E2 now contains:
Last Update Nov 16 2023
so the code saves/displays the pdf as expected (using a known filepath on my computer), saving it in your folder as Last Update Nov 16 2023.pdf
If you really need to show the date as mm/dd/yyyy in the pdf, that's possible if you replace the line of code above with this (changes in bold):
' Make a filename, replacing each / with -.
newFilename = "C:\Users\Steven\Desktop\" & _
WorksheetFunction.Substitute(ActiveSheet.Range("E2").Value, "/", "-") & _
".pdf"
and your file will be saved as Last Update 11-16-2023.pdf for example.
Hope this helps fix your problem. If so, please be sure to remember to mark this Answer as Selected.
