Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Excel – Create multiple PDFs based on a list

Create PDF from list - Featured image

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.

Download the example file

I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be helpful for future reference.

Download Icon
Download the file: 0073 Save PDFs based on a list.zip

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.

Example Report - for creating PDFs

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)

Data Table for Example Report - Creating PDFs from a list

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

Assign Macro to Button

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

Folder with PDFs saved

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.


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

11 thoughts on “Excel – Create multiple PDFs based on a list

  1. Tim Bellamy says:

    Hi,

    This is a great tutorial, I’ve been looking for something like this for a while.

    I have a macro that creates a pdf file and attaches it to an email with recipients and email body text included, would it be possible do you think to combine this with the macro here to open up multiple emails and attach each individual file? Or does the format of how this macro works prohibit this in someway?

    Thanks again

  2. Amir Permeh says:

    This is amazing. I have a 40 person sales team and this will save me hours of saving individual files. Is there a way to do this same thing but save file as an excel file instead of a pdf? Thanks for the awesome work.

  3. Luz says:

    Hello, thanks for this tutorial – very useful.

    I am trying to add the code to save the pdf files in the same location as the workbook is saved. Do you know is this is possible?

    Thank you

    • Excel Off The Grid says:

      Actually, I don’t think uploading the documents to a 3rd party site is an easier option. Also, this is likely to be in breach of most company IT policies. I can’t think of many organization that are happy for employees to upload sensitive information to a 3rd party websites.

  4. Sam says:

    Love this.
    I needed to include an auto fit for cell height since the incoming data size varied.
    Easy add with:
    Range(“[insert range]”).Select
    Selection.Rows.AutoFit

    Working on having it save as the name vs ID now. Would be helpful to include that along with how to add each file as an attachment to an email!

  5. Ismael says:

    Hello Mark

    Very usefull video, thanks.

    Quick question, as you can see, there are some blank lines between the line 19 and the data in the Filter function. Which code we can use there so that blank lines collapse before the file transformed into the PDF and expanded at the end of the process?

    Cheers

    • Excel Off The Grid says:

      For FILTER it will convert blank lines to 0, because it is a calculate inside the array.

      So you would use something like this:
      =FILTER(Data,Data[Item]<>0)

      However, if you’re getting your data through Power Query initially, you should be handling zero blank values in there. Therefore this ensures blank values are removed before it even gets into Excel.

  6. Peter says:

    Great tutorial!

    How can we enhance it to send the PDF’s to the student emails at the click of the button?

Leave a Reply

Your email address will not be published. Required fields are marked *