Print to PDF Using Macro Button in Excel (5 Macro Variants)

Working with PDFs in Excel is common among users. PDF printing is one of the repetitive instances users find themselves in. Excel print to PDF Macro Button can make PDF printing just one click away.

Let’s say we have an Excel Worksheet of Customer Name and Info, and we want to Print the Worksheet using the Macro CommandButton.

In this article, we demonstrate different VBA codes to print to PDF using Macro Button in Excel.

Macro Button & Its Insertion

Usually, users use the Keyboard Shortcuts key (i.e., F5) or Microsoft Visual Basic’s Run Toolbar > Run to execute a macro. As Excel users typically have to print Worksheets in PDF, they can use a Macro Button to instantly Save or Print.

🔄 Go to the Developer tab > Insert section > Click on Command Button (under ActiveX Controls).

🔄 Immediately, Excel inserts a Command Button. Right-click on the Button. Select Properties from the Context Menu options.

🔄 The Properties window opens up. Edit the Caption or other Options according to your requirements.

🔄 At last, to insert and assign a macro to this Command Button, Right-click on it. Then, select View Code from the options.

🔄 Excel brings up the Microsoft Visual Basic window and you see a similar depiction as shown in the image below.

After inserting a macro into this Microsoft Visual Basic window, you can simply run the macro using the Command Button. Each time you want to print a Worksheet in PDF, just click on the Macro Button; Excel will Save or Print the Worksheet or Worksheets in PDF.

Print to PDF through Macro Button in Excel: 5 Macro Alternatives

We can approach it in two different ways. The first one is- we can direct Print the Worksheet content with a simple click on the Macro Button. The other one is- we can use the Macro Button to save the Worksheet as a PDF file and then use Print Command to print the Worksheet. Follow the latter section to know the methods in detail.

Method 1: Use Macro Code to Print a Specific Worksheet in Excel

Step 1: Write the following macro in Microsoft Visual Basic window.

Private Sub CommandButton1_Click()
Dim PrintFile As String
PrintFile = Application.DefaultFilePath & "\" & _
ActiveWorkbook.Name & ".pdf"
Sheets("Sheet1").Select
ActiveSheet.PageSetup.PrintArea = "B7:E17"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=PrintFile, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

➤ in the code,

1 – declare the variable and assign the File type as PDF.

2 – the VBA Sheets.Select statement takes Sheet1 as about to print-worksheet. Also, the print area is supplied (i.e., B7:E17).

3 – export the file as PDF and ensure Print Properties as required.

Step 2: Click on the PRINT PDF Macro Button to run the macro.

Step 3: Excel publishes the worksheet as PDF and converts the data into PDF format. Click on the Print Icon as shown in the below image to print the Worksheet instantly.

🔺 Remember Excel has not saved the PDF file yet. If you want to save the file go to File > Save As or Save.

Method 2: Print Multiple Sheets Using Macro Button

This macro variant is similar to Method 1. However, this macro can print multiple Worksheets just by inserting multiple Worksheet names in the macro.

Step 1: Paste the following Macro into the Visual Basic window.

Private Sub CommandButton1_Click()
Dim PrintPDF As String
PrintPDF = Application.DefaultFilePath & "\" & _
ActiveWorkbook.Name & ".pdf"
Sheets(Array("Sheet1", "Sheet2")).Select
ActiveSheet.PageSetup.PrintArea = "B7:E17"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=PrintPDF, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

➤ From the above image, in the sections,

1 – introduce the variable and assign value to the variable.

2 – the VBA Sheets.Select statement takes multiple sheets using the VBA Array function.

3 – set the Print Area to a specific range.

4 – assign the PDF file properties and load the file after conversion as the OpenAfterPublish command is set to True.

Step 2: Use the Macro Button to run the macro. Repeat Step 3 of Method 1 to Save and Print the Worksheets in PDF format.

Method 3: Macro Button Save a Specific Range Using Macro Code to Print

Now, if we change the OpenAfterPublish command from True to False, Excel won’t open the PDF file automatically. Also, we can add a Save command to just save the PDF in our desired destination.

Step 1: Type the below macro in the Code section.

Private Sub CommandButton1_Click()
Dim PrintRng As Range
Dim SavePDF As String
Set PrintRng = Range("B7:E17")
SavePDF = ThisWorkbook.Path
PrintRng.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=SavePDF, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub

➤ The code’s sections,

1 – initiate the macro declaring the variables.

2 – set the variables. One of them directs the macro to save the PDF in the Workbook path.

3 – generate the Worksheet as a PDF file maintaining various properties. Don’t open the PDF file as the OpenAfterPublish command turned False.

Step 2: Afterward you click on the Macro Button, Excel saves the PDF file in the Workbook path. Open the PDF file then Follow Step 3 of Method 1 to print the file.

Method 4: Print Active Sheet Using Macro Button

Another variant of the macro can be used to save the Active Worksheet in PDF format and print it afterward.

Step 1: Use the below macro in the Code section.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\maruf\Desktop\Softeko\Excel Print to PDF Macro Button\PrintPDF.pdf", _
OpenAfterPublish:=False
Application.ScreenUpdating = True
End Sub

➤ The numbered sections,

1 – fix the Active sheet screening.

2 – export the Worksheet content as a PDF with a name (i.e., PrintPDF) and save it in the assigned location.

3 – reverse the Worksheet screening.

Step 2: Try clicking on the Macro Button to save worksheet content with an assigned name in PDF format. You see Excel stores the file in the assigned location.

Method 5: Print Active Worksheet with Error Handling

In case the saving in PDF format encounters errors, we can use a macro variant to show an error notification in the message box using an error handler.

Step 1: Insert the following macro in the Code section.

Private Sub CommandButton1_Click()
Dim wrkSheet As Worksheet
Dim wrkBook As Workbook
Dim FileName As String
Dim FilePath As String
Dim PDFFile As String
Dim PDFFilePath As String
Dim mrfFile As Variant
On Error GoTo errHandler
Set wrkBook = ActiveWorkbook
Set wrkSheet = ActiveSheet
FilePath = wrkBook.Path
If FilePath = "" Then
FilePath = Application.DefaultFilePath
End If
FilePath = FilePath & "\"
FileName = Replace(wrkSheet.Name, " ", "")
FileName = Replace(FileName, ".", "_")
PDFFile = FileName & "_" & ".pdf"
PDFFilePath = FilePath & PDFFile
mrfFile = Application.GetSaveAsFilename _
(InitialFileName:=PDFFilePath, _
FileFilter:="Save File (*.pdf), *.pdf", _
Title:="Select Location to Save the PDF File")
If mrfFile <> "False" Then
wrkSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=mrfFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox "Macro Button has created the PDF: "
End If
exitHandler:
Exit Sub
errHandler:
MsgBox "Error:Macro Button has created the PDF"
Resume exitHandler
End Sub

➤ From the above image, the code’s sections,

1 – declare the variables.

2 – assign the worksheet and workbook to values and direct the macro to go to the error handler in case any error occurs.

3 – assign the file path as default.

4 – set variables to values.

5 – using the VBA IF function macro exports the worksheet contents as PDF and display a confirmation message using VBA Message Box after finishing.

6 – error handler displays a preset notification in case any error occurs.

Step 2: After the macro insertion, click on the Macro Button to save the Excel content in PDF and error handle if any error occurs. The macro brings up the device directory to offer the location to save the PDF file.

Step 3: Excel displays a confirmation notification saying it has saved the PDF. Open the PDF the follow Step 3 of Method 1 to print the PDF.

You can simply modify the macro if you want to direct print the PDF file. By switching the OpenAfterPublish option from False to True, you can direct print any worksheet content’s PDF file.

Download Excel Workbook

Practice with the following Workbook. Replace added Macro with the alternative variants to compare outcomes.

Print to PDF Macro Button.xlsm

Conclusion

In this article, we demonstrate multiple variants of macro code to Excel Print to PDF Macro Button. Hope these variants excel in your instance. Comment, if you have further inquiries or have anything to add.

Get FREE Advanced Excel Exercises with Solutions!