Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Automated PDF Reports Using Excel VBA

Learn how to generate professional PDF reports automatically from Excel worksheets using VBA. This tutorial covers page setup, print areas, dynamic filenames, output folders, PDF export, error handling, and report verification.

1. Introduction to Automated PDF Reports

Businesses often need to share sales summaries, financial statements, monthly performance reports, and other Excel-based information with managers or clients. Sharing a workbook is not always convenient because the recipient may need a fixed-layout document that is easy to print, review, and distribute.

A PDF report preserves the exported page layout and is convenient for sharing and printing. Excel VBA can automate the process of preparing a report worksheet and exporting it to PDF.

In this capstone module, you will use the cleaned sales data from the previous stage to prepare a report and export it to a PDF file.

Learning objective: Create a VBA procedure that prepares an Excel report, configures its page layout, exports the selected worksheet as a PDF, and confirms that the output file has been created.

2. Why Automate PDF Report Generation?

  • Reduces repetitive manual export operations.
  • Uses a consistent report layout every time.
  • Supports regular daily, weekly, or monthly reporting workflows.
  • Allows reports to be saved in a designated output folder.
  • Can generate filenames based on report dates or periods.
  • Works together with data consolidation and analysis macros.
PDF export creates a report from the current worksheet content. It does not automatically clean the data, validate calculations, or decide which records belong in the report. Those steps should be completed before export.

3. Automated PDF Reporting Workflow

A typical automated reporting process starts with validated data. VBA prepares the report sheet, configures the page, exports the worksheet, and checks the result.

Validated Excel Data Consolidated and analyzed records Prepare Report Worksheet Titles, summaries, tables, and formatting Configure Page Layout Print area, orientation, margins, and scaling Export Using VBA ExportAsFixedFormat Save PDF and Verify Output Check file path and report appearance Shareable PDF Report Ready for review and distribution

Diagram 1: The complete workflow from validated Excel data to a verified PDF report.

4. Prepare the Workbook Structure

For this tutorial, the master workbook contains a worksheet named Master with cleaned sales data and another worksheet named Report for the formatted output.

Worksheet Purpose Example Contents
Master Stores consolidated and cleaned sales records. Order ID, Date, Branch, Product, Quantity, Price, Total.
Report Contains the printable report layout. Report title, report date, sales summary, and tables.
Suggested Folder Structure
Folder Structure
SalesAutomation/
|
|-- MasterSales.xlsm
|
|-- Reports/
    |-- Sales_Report_20261017.pdf
    |-- Sales_Report_20261018.pdf

In the examples below, the Reports folder is created beside the workbook if it does not already exist. The master workbook must be saved before these procedures are run.

5. Designing the Report Worksheet

The report worksheet should present important information clearly. For example, a sales report may include a title, report date, total sales, branch summaries, and a table of transactions.

Cell or Range Suggested Content
A1:G1 Company or report heading.
A2:G2 Report date or reporting period.
A4:B5 Summary metrics such as total sales.
A7:G7 Sales table column headings.
A8:G... Sales records to be included in the report.
Example: Prepare a Simple Report

The following macro copies the master sales records to the Report worksheet and adds a title and total sales value. It assumes the data follows the seven-column structure used in the previous consolidation tutorial.

VBA Code
Option Explicit

Sub PrepareSalesReport()

    Dim masterWS As Worksheet
    Dim reportWS As Worksheet
    Dim lastRow As Long

    Set masterWS = ThisWorkbook.Worksheets("Master")
    Set reportWS = ThisWorkbook.Worksheets("Report")

    lastRow = masterWS.Cells(masterWS.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No sales records are available.", vbExclamation
        Exit Sub
    End If

    reportWS.Cells.Clear

    ' Report title and date.
    reportWS.Range("A1:G1").Merge
    reportWS.Range("A1").Value = "Corporate Sales Report"

    reportWS.Range("A2").Value = "Report Date:"
    reportWS.Range("B2").Value = Date
    reportWS.Range("B2").NumberFormat = "dd-mmm-yyyy"

    ' Summary.
    reportWS.Range("A4").Value = "Total Sales"
    reportWS.Range("B4").Value = _
        Application.Sum(masterWS.Range("G2:G" & lastRow))

    reportWS.Range("B4").NumberFormat = "#,##0.00"

    ' Copy the table headings and records.
    reportWS.Range("A7:G" & lastRow + 5).Value = _
        masterWS.Range("A1:G" & lastRow).Value

    ' Format the report.
    With reportWS

        .Range("A1:G1").Font.Bold = True
        .Range("A1:G1").Font.Size = 16
        .Range("A1:G1").HorizontalAlignment = xlCenter

        .Range("A7:G7").Font.Bold = True
        .Range("A7:G7").WrapText = True

        .Columns("A:G").AutoFit

        .Range("B8:B" & lastRow + 5).NumberFormat = "dd-mmm-yyyy"
        .Range("F8:G" & lastRow + 5).NumberFormat = "#,##0.00"

    End With

    MsgBox "Sales report prepared successfully.", vbInformation

End Sub
Explanation
  • masterWS refers to the worksheet containing the source data.
  • reportWS refers to the worksheet that will be exported.
  • lastRow identifies the final populated Order ID row.
  • Application.Sum calculates the total of the sales amount column.
  • Merge combines the title cells into one heading area.
  • The data table is copied below the title and summary.
This sample clears the existing Report worksheet before rebuilding it. Use a dedicated report sheet and preserve any information that must not be overwritten. Ensure that invalid transactions have been handled before calculating the official total.

6. Configure Page Setup and Print Area

A worksheet can look good on screen but produce an awkward PDF when its columns span multiple pages or its content is clipped. Page setup controls how the worksheet is laid out for printing and PDF export.

Important Page Setup Properties
Property Purpose
Orientation Sets portrait or landscape page orientation.
PrintArea Defines the cells to include in the printed output.
Zoom Controls print scaling when enabled.
FitToPagesWide Sets how many pages wide the output should fit.
FitToPagesTall Sets how many pages tall the output should fit.
CenterHorizontally Centers the report horizontally on the page.
RepeatRows Repeats selected heading rows on subsequent pages.
Example: Configure the Report Page
VBA Code
Sub ConfigureReportPage()

    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Report")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 1 Then
        MsgBox "The report worksheet is empty.", vbExclamation
        Exit Sub
    End If

    With ws.PageSetup

        .PrintArea = ws.Range("A1:G" & lastRow).Address

        .Orientation = xlLandscape
        .PaperSize = xlPaperA4

        .LeftMargin = Application.InchesToPoints(0.3)
        .RightMargin = Application.InchesToPoints(0.3)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)

        .CenterHorizontally = True

        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False

        .PrintTitleRows = "$7:$7"

    End With

    MsgBox "Report page setup completed.", vbInformation

End Sub

The macro sets the print area, selects landscape orientation, uses A4 paper, adjusts margins, and fits the report to one page wide. The heading in row 7 repeats on subsequent pages.

Scaling tip: Fit-to-one-page-wide is useful for reports with many columns. If there are many rows, forcing the entire report onto one page vertically can make the text unreadable. The example allows multiple pages vertically.

7. Understanding PDF Page Layout

The goal is to make the report readable without cutting off columns or shrinking the text excessively.

Poor Layout Report title Columns may be clipped Proper Layout Sales Report Clear columns and readable margins Preview and test the PDF before distributing it.

Diagram 2: Good page setup helps avoid clipped columns and unreadable output.

8. Export the Worksheet as a PDF

Excel VBA provides the ExportAsFixedFormat method for exporting a worksheet or workbook to a fixed-format file such as PDF. The following example exports the Report worksheet.

Example: Export a Report to PDF
VBA Code
Sub ExportReportToPDF()

    Dim ws As Worksheet
    Dim outputFolder As String
    Dim pdfPath As String

    On Error GoTo ErrorHandler

    If Len(ThisWorkbook.Path) = 0 Then
        MsgBox "Save the workbook before exporting the report.", vbExclamation
        Exit Sub
    End If

    Set ws = ThisWorkbook.Worksheets("Report")

    outputFolder = ThisWorkbook.Path & _
                   Application.PathSeparator & "Reports"

    If Len(Dir(outputFolder, vbDirectory)) = 0 Then
        MkDir outputFolder
    End If

    pdfPath = outputFolder & _
              Application.PathSeparator & _
              "Sales_Report_" & Format(Date, "yyyymmdd") & ".pdf"

    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    If Len(Dir(pdfPath)) > 0 Then
        MsgBox "PDF report created successfully:" & vbCrLf & _
               pdfPath, vbInformation
    Else
        MsgBox "The PDF file could not be verified.", vbExclamation
    End If

    Exit Sub

ErrorHandler:

    MsgBox "PDF export failed: " & Err.Description, vbExclamation

End Sub
Code Explanation
  • ThisWorkbook.Path identifies the folder containing the master workbook.
  • Dir checks whether the Reports folder or output file exists.
  • MkDir creates the Reports folder when it is missing.
  • Format(Date, "yyyymmdd") creates a date-based filename.
  • xlTypePDF selects PDF as the export format.
  • IgnorePrintAreas:=False respects the worksheet's configured print area.
  • OpenAfterPublish:=False prevents Excel from automatically opening the exported PDF.
If a PDF with the same date-based filename already exists, the export may overwrite that file. Use a unique timestamp or an explicit overwrite policy if reports must be preserved separately.

9. Creating Dynamic PDF File Names

Dynamic filenames make it easier to identify reports by date, month, branch, or report type. Use characters that are valid in Windows filenames.

Example: Filename with Date and Time
VBA Code
Sub GenerateTimestampedPDFName()

    Dim reportName As String

    reportName = "Sales_Report_" & _
                 Format(Now, "yyyymmdd_hhnnss") & ".pdf"

    MsgBox "Generated filename: " & reportName, vbInformation

End Sub

The Now function returns the current date and time. The format string uses nn for minutes in VBA's date formatting syntax.

Filename Pattern Example Use
Sales_Report_yyyymmdd.pdf Sales_Report_20261017.pdf One report per day.
Sales_Report_yyyymmdd_hhnnss.pdf Sales_Report_20261017_143025.pdf Separate exports by time.
Branch_Pune_yyyymmdd.pdf Branch_Pune_20261017.pdf Branch-specific reports.

10. Complete Automation: Prepare and Export

After the report worksheet has been prepared, page setup can be applied and the PDF can be exported. The following procedure combines the operations into one workflow.

VBA Code
Sub GenerateSalesPDFReport()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim outputFolder As String
    Dim pdfPath As String

    On Error GoTo ErrorHandler

    If Len(ThisWorkbook.Path) = 0 Then
        MsgBox "Save the workbook before running the report.", vbExclamation
        Exit Sub
    End If

    Set ws = ThisWorkbook.Worksheets("Report")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 7 Then
        MsgBox "The report does not contain a complete data table.", vbExclamation
        Exit Sub
    End If

    ' Configure the print area and page layout.
    With ws.PageSetup

        .PrintArea = ws.Range("A1:G" & lastRow).Address

        .Orientation = xlLandscape
        .PaperSize = xlPaperA4

        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False

        .CenterHorizontally = True
        .PrintTitleRows = "$7:$7"

    End With

    ' Create the output folder if necessary.
    outputFolder = ThisWorkbook.Path & _
                   Application.PathSeparator & "Reports"

    If Len(Dir(outputFolder, vbDirectory)) = 0 Then
        MkDir outputFolder
    End If

    ' Use a timestamp to reduce filename collisions.
    pdfPath = outputFolder & _
              Application.PathSeparator & _
              "Sales_Report_" & _
              Format(Now, "yyyymmdd_hhnnss") & ".pdf"

    ' Export the report worksheet.
    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    ' Verify that the PDF exists.
    If Len(Dir(pdfPath)) = 0 Then
        Err.Raise vbObjectError + 2001, _
                  "GenerateSalesPDFReport", _
                  "The PDF file could not be found after export."
    End If

    MsgBox "Sales PDF report created successfully." & vbCrLf & _
           pdfPath, vbInformation

    Exit Sub

ErrorHandler:

    MsgBox "Report generation failed: " & _
           Err.Description, vbExclamation

End Sub

This procedure assumes that the Report worksheet has already been populated with the correct title, summary, and table. It configures the page and exports the worksheet, but it does not itself refresh or validate the underlying sales calculations.

11. Understanding the Final PDF Output

After export, the PDF should contain the report heading, summary information, column headings, and the selected records in a readable layout.

CORPORATE SALES REPORT Report Date: 17-Oct-2026 Total Sales Summary Value Order ID Branch Product Amount ORD1001 Pune Laptop 90,000 ORD1002 Mumbai Monitor 36,000 ORD1003 Nashik Keyboard 7,500 Example report layout Verify the actual exported PDF before distribution.

Diagram 3: Illustrative report layout showing a title, summary, and sales table. The actual values come from your workbook.

12. Common Errors and Their Solutions

Problem Possible Cause Solution
Workbook path is empty The workbook has not been saved. Save the workbook before running the macro.
Report worksheet not found The worksheet name is incorrect or missing. Verify that the worksheet is named Report.
PDF contains extra blank pages Print area includes unnecessary rows or columns. Set the print area to the actual report range.
Columns are cut off Page orientation or scaling is unsuitable. Review landscape orientation and page width scaling.
Text is too small Too much content is forced onto one page. Allow more pages or simplify the report layout.
Cannot create output folder Insufficient permissions or an invalid path. Check the destination path and write permissions.
Existing report is replaced The filename is reused. Use a unique filename or an explicit retention policy.
Report totals are incorrect The source data or calculation rules are incorrect. Validate the dataset and calculations before export.

13. Best Practices for Automated PDF Reports

  • Validate the data before preparing the report.
  • Use a dedicated report worksheet rather than modifying source data.
  • Set the print area explicitly.
  • Choose the page orientation based on the number of columns.
  • Use readable font sizes and appropriate margins.
  • Use unique filenames when reports must be retained separately.
  • Handle export errors and verify that the PDF exists.
  • Check the exported PDF visually before sharing it.
  • Store generated reports in an approved folder with suitable access permissions.

14. Practical Exercises

  1. Create a Report worksheet containing a title, report date, summary, and sales table.
  2. Set the print area dynamically based on the last populated row.
  3. Export the Report worksheet to a PDF file.
  4. Modify the filename to include the current date and time.
  5. Configure landscape orientation and fit the report to one page wide.
  6. Repeat the column headings on subsequent pages.
  7. Create the Reports folder automatically when it is missing.
  8. Handle an empty report worksheet and a missing workbook path.
  9. Open the exported PDF and verify the report's content and layout.

15. Important Points to Remember

  • ExportAsFixedFormat is used to export worksheets or workbooks to PDF.
  • PageSetup controls print orientation, margins, scaling, and print area.
  • Always prepare and validate the report before exporting it.
  • A date-based filename can be reused, so consider timestamped names.
  • Folder permissions and existing files can affect the export process.
  • Successful export does not guarantee a readable page layout.
  • Verify the final PDF before distributing it.

Summary

Automated PDF Reports using Excel VBA help transform worksheet information into consistent, shareable documents. The process includes preparing the report worksheet, configuring the print area and page layout, generating a suitable filename, exporting the PDF, and verifying the output.

Use error handling, readable page settings, appropriate filenames, and verified data to make the reporting workflow reliable. The next capstone module will use these results alongside the cleaned data to build Dashboard Automation.