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


Download Practice Workbook

You can download the practice workbook from here.


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

Throughout this article, we will explain 3 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.

3 Effective Ways to Generate a Report in PDF Format Using Excel VBA


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:

Using Excel VBA to Add Command Button to Generate Report in PDF Format

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

Using Excel VBA to Add Command Button to Generate Report in PDF Format

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

Using Excel VBA to Add Command Button to Generate Report in PDF Format

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

Using Excel VBA to Add Command Button to Generate Report in PDF Format

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

Using Excel VBA to Add Command Button to Generate Report in PDF Format

  • 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 on 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 Reports from Excel Data (2 Easy Methods)


Similar Readings


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.

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

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

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

  • 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 Create a Summary Report in Excel (2 Easy Methods)


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.

Report Generating in PDF Format from Multiple Sheets with Excel VBA

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.

Report Generating in PDF Format from Multiple Sheets with Excel VBA

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

Report Generating in PDF Format from Multiple Sheets with Excel VBA

  • 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 (3 Easy Ways)


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. Keep an eye out for more inventive Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo