Bulk PDF export loop

1. One line: sheet to PDF

Worksheets("Invoice").ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & Application.PathSeparator & "Invoice.pdf", _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

The PDF follows the sheet's Page Layout settings: print area, orientation, margins, Fit to 1 page wide. Set those once on the template, and every PDF looks right.

2. Simple loop: every sheet as its own PDF

Sub ExportEverySheet()
    Dim sh As Worksheet, folder As String
    folder = ThisWorkbook.Path & Application.PathSeparator
    For Each sh In ThisWorkbook.Worksheets
        If sh.Visible = xlSheetVisible Then
            sh.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=folder & sh.Name & ".pdf"
        End If
    Next sh
End Sub

3. The real job: one PDF per customer from a template

Sheet "Customers" (list):

Sheet "Statement" (template): cell B3 holds the CustomerID. Everything else — name, address, list of invoices, total — is pulled with XLOOKUP/SUMIFS/FILTER formulas based on B3. Change B3, and the whole statement changes.

The macro just changes B3, recalculates, and exports — again and again.

Option Explicit

Sub ExportStatements()
    Dim wsList As Worksheet, wsTpl As Worksheet
    Dim lastRow As Long, i As Long, count As Long
    Dim folder As String, custID As String, custName As String
    Dim calcMode As XlCalculation

    On Error GoTo ErrHandler
    Set wsList = ThisWorkbook.Worksheets("Customers")
    Set wsTpl = ThisWorkbook.Worksheets("Statement")
    lastRow = wsList.Cells(wsList.Rows.Count, "A").End(xlUp).Row

    folder = ThisWorkbook.Path & Application.PathSeparator & "PDF_" & Format(Date, "yyyy-mm")
    If Dir(folder, vbDirectory) = "" Then MkDir folder

    calcMode = Application.Calculation
    Application.ScreenUpdating = False

    For i = 2 To lastRow
        custID = Trim(CStr(wsList.Cells(i, "A").Value))
        custName = Trim(CStr(wsList.Cells(i, "B").Value))
        If Len(custID) > 0 Then
            wsTpl.Range("B3").Value = custID
            Application.Calculate                    ' make sure formulas update

            wsTpl.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=folder & Application.PathSeparator & custID & "_" & custName & ".pdf", _
                Quality:=xlQualityStandard, IgnorePrintAreas:=False
            count = count + 1
        End If
    Next i

    MsgBox count & " PDFs saved in:" & vbCrLf & folder, vbInformation

CleanExit:
    Application.Calculation = calcMode
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox "Stopped at row " & i & ": " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

File names like C001_Rahul Sharma.pdf make them easy to find and to attach in the next lesson.

4. Skip customers with nothing to send

If the template shows a total in, say, F30, skip zero-balance customers:

If wsTpl.Range("F30").Value <> 0 Then
    ' export
End If

5. Several sheets in one PDF

Select them as a group and export the active selection:

ThisWorkbook.Worksheets(Array("Summary", "Details")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=folder & "\Report.pdf"
Worksheets("Summary").Select     ' ungroup afterwards

This is one of the few places where .Select is genuinely needed.

6. Page setup checklist for good PDFs

On the template: Page Layout → Print Area set; Orientation chosen; Width: 1 page; Print Titles if the table runs over pages; header/footer with page numbers. Check File → Print preview once before running the loop.

Common mistakes

No print area, so the PDF includes empty pages or extra columns. Forgetting Application.Calculate when calculation is manual (every PDF shows the first customer). Invalid characters in names (reuse SafeName from Lesson 1). Leaving sheets grouped after a multi-sheet export.