How to Generate Report in PDF Format Using Excel VBA (3 Quick Tricks)

This tutorial will illustrate how to generate a report in PDF format using Excel VBA. Suppose a user has to create reports every day for his company. He keeps the records of the report in an Excel worksheet. But, in the end, he wants to generate the report in PDF format. It’s necessary because PDF restricts a user from any kind of editing. So, in this article, we will demonstrate how a user can generate a report in PDF format from an Excel worksheet easily. To do this, we will use VBA macros.

How to Generate a Report in PDF Format Using Excel VBA: 3 Quick Tricks

Throughout this article, we will explain three quick tricks of using VBA to generate a report in PDF format. To illustrate the methods, we will use the following dataset. The dataset depicts a sales report. It contains the name, city, and sales amount of different persons. The user keeps this record in an Excel sheet. Now we will see how he can generate it in PDF format using VBA macro.

1. Use Excel VBA to Add Command Button to Generate Report in PDF Format

First and foremost, we will insert a command button in our Excel worksheet. It will generate the report in PDF format in one click using VBA. To do this first, we will create a command button. Then, we will assign a VBA macro to that button. The VBA macro will convert the Excel report into PDF format. Let’s see the steps to perform this action.

STEPS:

• To begin with, go to the Developer tab. Click on Macros from the ribbon.

• In addition, type Macro_1 in the text field Macro name.
• Then, click on the Create button.

• The above action will open a blank code window.
• Furthermore, type the following code inside the VBA macro:

Sub Macro_1()
ChDir "C:\Users\User\Desktop\ExcelDemy"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\User\Desktop\ExcelDemy\Generate Report in PDF.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub

• Now, click on the Save icon or press Ctrl + S to save the code. Then, close the window.
• Here, the highlighted line in the code indicates the location where we will save the PDF format.

• Also, go to the Developer tab.
• Select the Button option from the Insert tab.

• Afterward, draw a button. Then, right-click on the button. Select the Edit Text option.

• Following, rename the button to ‘Generate Report to PDF’. You can rename the button as per your choice also.

• Subsequently, right-click on the button. Select the option Assign Macro also.

• Moreover, select Macro-1 and click OK.

• After that, click on the button ‘Generate Report in PDF’.

• The above command generates the excel report in PDF format.
• Lastly, go to the folder location that we used in the code. We can see the PDF report in that folder.

Read More: How to Generate PDF Reports from Excel Data

2. Create Report Using Excel VBA in PDF Format with Specific File Name

In the second method, we will generate a report in PDF format with specific file names. We will do this using Excel VBA. So, we can name the PDF file as per our choice rather than using sheet names. To do this we will follow the below steps.

STEPS:

• First of all, go to the Developer tab. Also, select the option Visual Basic from the ribbon.

• A new VBA project window will open.
• Also, right-click on Sheet2 (Save with Name).
• Additionally, select Insert > Module.

• So, the above command opens a blank VBA code window.
• Moreover, insert the following code in that blank code window:

Sub Report_Name()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="Report PDF", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
From:=1, _
To:=5, _
OpenAfterPublish:=True
End Sub

• Now, press the F5 key or click on the Run button to run the code.
• Here, the highlighted line in the code indicates the new name of or PDF format.

• Finally, the excel sheet opens in PDF format. Also, We can see the name of the PDF file in the following image.

Read More: How to Generate Reports in Excel Using Macros

3. Generate Report in PDF Format from Multiple Sheets with Excel VBA

Sometimes, we have to take data from multiple sheets to create a report. So, in the last method, we will generate a report in PDF format from multiple sheets using Excel VBA. In the following two images, we have two sales data from different sheets. We will create a report in another sheet using these two sheets.

Follow the below steps to perform this method.

STEPS:

• Firstly, go to another sheet named Report.
• Secondly, right-click on the sheet. Also, select the option View Code.

• So, a new blank VBA code window will appear.
• Thirdly, insert the following code in that code window:

Private Sub Workbook_Open()
Dim nextblankrow As Long
Dim lastrow As Long
Sheets("Report").Select
Sheets("Report").Cells.ClearContents
Range("A1").Value = "Name"
Range("B1").Value = "City"
Range("C1").Value = "Sales Amount"
For i = 1 To Sheets.Count - 1
lastrow = Sheets(i).Range("A" & Rows.Count).End(xlUp).Row
Sheets(i).Range("A2:C6").Copy
Sheets(i).Range(Cells(2, 1).Address, Cells(lastrow, 4).Address).Copy
nextblankrow = Sheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Report").Cells(nextblankrow, 1).PasteSpecial Paste:=xlPasteValues
Next
Cells(1, 5).Select
Application.CutCopyMode = False
Sheets("Report").Range(Cells(1, 1), Cells(nextblankrow, 4)).Columns.AutoFit
ChDir "E:\Exceldemy"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"E:\Exceldemy\Generate Reeport.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
End Sub

• Additionally, to run the code, press the F5 key or click ok the Run button.

• So, we get results like the following image. Data from two sheets get merged into the sheet named Report.

• Lastly, browse the folder we specified in the code. In that folder, we can see the PDF file.

Read More: How to Automate Excel Reports Using Macros

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In conclusion, this tutorial will guide you on how to generate a report in PDF format using Excel VBA. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will also reply to you as quickly as possible.

Related Articles

• How to Make Report Card in Excel
• Create a Report in Excel as a Table
• How to Generate Reports from Excel Data
• How to Create a Summary Report in Excel

<< Go Back to Report in Excel | Learn Excel