Excel – Create multiple PDFs based on a list

I was on a call recently where somebody asked if it was possible to create a unique PDF for each item in a list. The answer is “Yes”, so in this post, I want to show you how to do it; how to create multiple PDFs based on a list in Excel.

Table of Contents

• Scenario
• VBA macro
• Adapting the code to your scenario
• Testing it out

Watch the video

Watch the video on YouTube.

Scenario

The scenario we are looking at is reasonably straightforward, so you should be able to adapt it to your specific circumstances.

In the example file, we want to create an exam report for each student based on their ID number (see screenshot below).

When the Student ID value in Cell G4 changes, the report recalculates to return the results for the selected student.

Cell G4 is based on a data validation list, using the values starting in Cell I4.

There is a shape labeled Save PDFs; we will assign the macro to this button later.

The entire report is based on data contained in an Excel Table (see screenshot below)

I have used XLOOKUP, UNIQUE, and FILTER functions for ease of demonstration. Therefore, the example file will only function correctly if you have a dynamic array-enabled version of Excel (i.e., it won’t work on Excel 2019 and earlier). However, I have not used any dynamic array functionality in the VBA macro. You should be able to adapt the code, even if you do not have a dynamic array-enabled version of Excel.

VBA macro

Insert the code below into a standard code module.

Sub SavePDFsFromList()

'Declare the Variables
Dim ws As Worksheet
Dim rngID As Range
Dim rngListStart As Range
Dim rowsCount As Long
Dim i As Long
Dim pdfFilePath As String
Dim tempPDFFilePath As String

'Stop the screen updating while running
Application.ScreenUpdating = False

'Reference Report Sheet
Set ws = ActiveWorkbook.Sheets("Report")

'Reference the Student ID cell
Set rngID = ws.Range("G4")

'Reference the start of the Studend ID List
Set rngListStart = ws.Range("I4")

'Count the rows in the Student ID List
rowsCount = rngListStart.CurrentRegion.Rows.Count - 1

'Create the PDF File Name
pdfFilePath = "C:\Test Folder\PDF Export\Student Exam Record - [ID].pdf"

For i = 1 To rowsCount

    'Change the current student ID
    rngID.Value = rngListStart.Offset(i - 1, 0).Value

    'Replace [ID] with the Student ID Value
    tempPDFFilePath = Replace(pdfFilePath, "[ID]", rngID.Value)

    'Create the PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        fileName:=tempPDFFilePath

Next i

'Restart the screen updating
Application.ScreenUpdating = True

End Sub

Adapting the code to your scenario

There are too many scenarios where this type of solution could be applied. Therefore, you will need to adapt the code above to meet your specific requirements. The essential items to change are:

Set rngID = ws.Range("G4")

The cell reference G4 should be changed to be the parameter that causes the report to change.

Set rngListStart = ws.Range("I4")

The cell reference I4 should be the first cell in the list of items to loop through.

pdfFilePath = "C:\Test Folder\PDF Export\Student Exam Record - [ID].pdf"

The pdfFilePath should contain a valid folder path on your PC or network.

The string of [ID] identifies the reference that should be replaced within the PDF file path. This text string needs to be the same in the code above and below.

tempPDFFilePath = Replace(pdfFilePath, "[ID]", rngID.Value)

Testing it out

Now, let’s assign the macro to the shape so we can test it out.

• Right-click on the shape
• Select Assign Macro… from the menu
• In the Assign Macro dialog box, select the macro
• Click OK

Everything is now set up; let’s test it out. Click the Save PDFs button.

We have the individual student reports all saved down in a few seconds. So even if you had hundreds, it really shouldn’t take long 😀.

If you want to look deeper into the topic of saving PDFs with VBA macros, then check out this post: Save Excel as PDF with VBA.

---

What next?

Discover how you can automate your work with our Excel courses and tools.

The Excel Academy