On a previous article, dated 2 years ago, I explained how to automatically print to pdf a sheet, with a press of a button.

This one is the part 2. It is a tutorial of “how to print multiple pdf files from populated tables” in excel, with the help of VBA.

I wrote the code in 2018 and since then it has saved me (or the company I work for) a massive number of working hours (or weeks to be more precise).

There is often the need to print to pdf, populated parts of a big table, one by one.

There might be a salary breakdown or a small contract that must be populated and printed in multiple files like the one below. So far and as I have seen, the procedure was like the one below (also can download the files from here):

The workflow that a normal excel user would follow is to print to pdf the populated by the drop-down cell pages, one by one. Of course, this procedure works but it is time consuming, especially when there are hundreds of pages that need to be printed.

In the example below I will show you a way to do it automatically with a little help of VBA (a lot of help to be honest)!

Let’s say we have a huge salary table that calculates employee payments and then populates them to a separate sheet (named as “Salary Breakdown”). There is a drop-down cell that lets you select one by one the payment ID and by selecting an ID all the other cells change automatically. This sheet should be printed on one page and on the back page, the pdf should contain the “Terms” (dummy Lorem Ipsum text, sheet 3).

The code to be used is the below:

Option Explicit
Sub PrintAllAsPDF()
Dim dvCell As Range
Dim inputRange As Range
Dim c As Range
Set dvCell = Sheets("Salary Breakdown").Range("B2")
Set inputRange = Evaluate(dvCell.Validation.Formula1)
    
    For Each c In inputRange
        dvCell = c.Value

Dim strFileName As String

Dim strEmployeeName As String

Dim strEmployeeID As String

strFileName = Replace(ThisWorkbook.Name, ".xlsm", "")

strEmployeeName = Sheets("Salary Breakdown").Range("B1").Value

strEmployeeID = Sheets("Salary Breakdown").Range("B2").Value

ThisWorkbook.Sheets(Array("Salary Breakdown", "Terms")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & strFileName & " - ID_" & strEmployeeID & " - " & strEmployeeName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

Sheets("Salary Breakdown").Select

Next

End Sub

Let’s explain what the code does and what is needed to be changed, but before we do this we should have already been set to “Page Break Preview” that is located at the right bottom of the Excel program.

To make the “Developer” mode appear on the menu bar and that will let you write VBA code in Excel, follow this link and this video.

Let’s explain the code:

Dim dvCell As Range
Dim inputRange As Range
Dim c As Range

Here, we declare the variables (do not have to change anything here).

Set dvCell = Sheets("Salary Breakdown").Range("B2")
Set inputRange = Evaluate(dvCell.Validation.Formula1)

This part is important. The user must change the B2 cell to the one that has the Data Drop-Down (data validation) in the excel sheet. Then the code evaluates the cell as data validation cell and uses the Formula1 (VBA method). There is no need to do anything on the second line of code.

For Each c In inputRange
        dvCell = c.Value

Here the user doesn’t have to do anything. We are telling the program to make a pass through the different variables of the drop-down list of the inputRange that was set above, and for each value, change each time the dvCell variable with the value of the cell. I believe this is the most important part of the code.

Dim strFileName As String
Dim strEmployeeName As String
Dim strEmployeeID As String

Declaring some string variables that will be used below to get the values of some cells and use them in order to create different file names to each pdf that will be produced.

strFileName = Replace(ThisWorkbook.Name, ".xlsm", "")
strEmployeeName = Sheets("Salary Breakdown").Range("B1").Value
strEmployeeID = Sheets("Salary Breakdown").Range("B2").Value
ThisWorkbook.Sheets(Array("Salary Breakdown", "Terms")).Select

Here are some parts of the code that the user must do some changes.

First of all, I generally use the excel file name to name the pdfs. The first line does exactly this. It sets the value of the variable strFileName (set above), as the file’s name but it replaces the .xlsm excel file extension name with an empty string. On this line the user doesn’t have to do anything.

Secondly, the code takes the value of the cell that contains the employee’s name and sets the setEmployeeName variable with its value. B1 cell can change.

The same applies to the strEmployeeID variable. The user can change this cell to the one that has the data validation drop down. Finally, another important change that the user has to do is to change the names of the “Salary Breakdown” and “Terms” to the sheets that they want to print. If there are more sheets needed to be printed, then in the Array("Salary Breakdown", "Terms") they need to add another value like: ThisWorkbook.Sheets(Array("Salary Breakdown", "Terms","Another Sheet")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & strFileName & " - ID_" & strEmployeeID & " - " & strEmployeeName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

This part of the code says that:

1. Use this work path (the folder we are working in) to export the generated pdf files.
2. Use the strFileName variable as name
3. Then use – ID_ in the name
4. Then use the strEmployeeID variable in the name
5. And finally, add a “-“ and the employee’s name.
6. The other 2 lines of code declare how the pdf should be printed and the user doesn’t have to do something.

The final parts of the code are:

Sheets("Salary Breakdown").Select
Next
End Sub

Which mean do the whole process in a loop until the data validation cell comes to an end of the drop-down values and then the “Salary Breakdown” sheet will be selected as the main working sheet.

The user needs to add a button and connect it to this VBA module (that on the second line of the code I have given a name of PrintAllAsPDF).

And that’s all! After the press of the button the pdfs will start appearing into the folder that the excel exists (I suggest not to use the desktop folder).

I have also created 2 files that whoever is interested can download and try to play with the code.