Adding Save as PDF button ERROR

Soumbrero

Clicking the existing button on worksheet "Invoice" causes the sub CreatePDF to run.

That should create and open a pdf of the invoice BUT if you click it again without first closing the pdf, you'll get an error (since the code can't save when the same named file is open).and this line will appear in yellow (if you click Debug):

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=newFilename, _
        Quality:=xlQualityStandard, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

If you halt the code, close the pdf and reclick, it should run again without problem.

Once you're confident the code works, you can get around that possible error by changing the end of the line as follows (in bold) so the pdf isn't opened:

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=newFilename, _
        Quality:=xlQualityStandard, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

If I may, I'd like to suggest a small improvement.... your line:

    ' Make a filename.
    newFilename = "C:\Test\Export\" & _
        ActiveSheet.Range("E10").Value & ".pdf"

produces filenames like "41.pdf".

It may be more meaningful to name the file like "Invoice #41.pdf" and further add the date of printing (since invoices sometimes need to be corrected).

To get file names like "Mar 24 2025 Invoice #41.pdf", I've changed the code in the attached file as follows (to not open the fuller named pdf but to give the user a message to say the file has been saved):

Sub CreatePDF()

' Create PDF

    ' Variables
    Dim newFilename As String

    ' Make a filename.
    newFilename = "C:\Test\Export\" & _
    Format(Date, "mmm dd yyyy") & _
    " Invoice #" & ActiveSheet.Range("E10").Value & ".pdf"


    ' Make the PDF.
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=newFilename, _
        Quality:=xlQualityStandard, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    ' Tell the user
    MsgBox "Saved as " & newFilename


End Sub

Revision #1, 28 March 2025:

Soumbrero- I see from your later question that you are using a MacBook Pro, in which case the folder:

C:\Test\Export\

will NOT exist since MAC OS uses the forward slash (/) in filepaths, not back slash (\) as in Windows. (and you'll get the 1004 error).

In the file I provided, please change the line:

    ' Make a filename.
    newFilename = "C:\Test\Export\" & _
        ActiveSheet.Range("E10").Value & ".pdf"

to read a filepath that exists on your Mac, e.g.

    ' Make a filename.
    newFilename = "/Users/<< your Mac user name>>/Documents/" & _
        ActiveSheet.Range("E10").Value & ".pdf"

Hope this helps and sorts your problem. If so, please remember to mark this Answer as Selected.