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

For this tutorial, we’ll assume you have an Excel Worksheet titled Customer Name and Info, and you want to print the worksheet using the Macro CommandButton.

Dataset-Excel Print to PDF Macro Button

 

Macro Button & Its Insertion

Typically, Excel uses leverage 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).

Command Button-Excel Print to PDF Macro Button

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

Properties window

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

Alphabetic changes

To insert and assign a macro to this Command Button, right-click on it. Select View Code from the options.

Opening Microsoft Visual Basic

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

Inserting space for macro

After inserting a macro into this Microsoft Visual Basic window, you can simply run the macro using the Command Button.


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

If you’d like an alternative to the above method, the following are five more methods you can try in Excel.


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

Step 1: Write the following macro in the 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

Method 1-Excel Print to PDF Macro Button

 ➤ 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 a PDF and ensure Print Properties as required.

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

Macro Button

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.

PDF file

If you want to save the file go to File > Save As or Save.

Save PDF

Read More: Excel VBA to Print As PDF and Save with Automatic File Name


Method 2 – Print Multiple Sheets Using Macro Button

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

Method 2-Excel Print to PDF Macro Button

➤ 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.

Macro Button


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

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

Method 3-Excel Print to PDF Macro Button

 ➤ 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 is turned False.

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

Device directory

Read More: Print Range to PDF with VBA in Excel


Method 4 – Print Active Sheet Using Macro Button

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

Method 4-Excel Print to PDF Macro Button

 ➤ 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: Click the Macro Button to save the worksheet content with an assigned name in PDF format. Excel stores the file in the assigned location.

Device directory


Method 5 – Print Active Worksheet with Error Handling

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

macro

➤ From the above image, the code’s sections are as follows:

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.

File saving option

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.

Confirmation

You can modify the macro if you want to direct print the PDF file by switching the OpenAfterPublish option from False to True.


Download Excel Workbook

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


Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo