Home/ Questions/Q 2981
Next
Answered
computing
  • 0
Asked: 2021-11-01T23:42:52+00:00 In:

Solved Macro Button Code, SaveAs PDF w/ File Name From Cell

  • 0

To whom this may concern,

I have a work sheet that is an office form, I would like to have a button on the form that that can do 3 tasks at one time:

1. Save Sheet as a PDF
2. Save the PDF with the file name from cell’s “O30” & “O31” & “A1” with a space of dash between the three names
3. Save the PDF in a folder on the desktop.

Thanks you for you help and support in advance.
-E925

Share

    1 Answer

    1. Best Answer
      The code below should do what you ask with the following caveats:

      1 – I am assuming that the cells you referenced are on Sheet1. Modify as required
      2 – You will need to put the filepath to the Desktop where indicated. It must inside the opening quote and before the \Desktop.

      Sub DesktopPDF()

'Set path to Desktop
    fPath = " ***Insert Path To Desktop Here*** \Desktop"

'Build File Name from Sheet1 O30, O31 & A1
    fName = Sheets(1).Range("O30") & "-" & _
            Sheets(1).Range("O31") & "-" & _
            Sheets(1).Range("A1")

'Export as PDF to Desktop
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
        
End Sub

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0