Excel Batch Printing

Batch Printing using VBA Loop

 
Excel VBA is very powerful in automating tasks. Looping is one of the most useful feature in this category. We will demonstrate in this tutorial how to loop in a range and batch print by pressing only one button.

Description of Example
The objective is to print the 'Certificate of Attendance' for each person listed in column L. In this example we have 50 individuals and we will automate the printing of their certificates using VBA loop.
 
Batch Print in Excel
 

How is it done
First step is to set-up the layout and the printer. Go to File > Print, make sure to use the correct printer and to check the preview on the right side of window, adjust the page as necessary. When a pdf printer is selected, please customize the filename to auto-number or to merge files because this will print multiple pages.
 
Excel Batch Print 2
 

Insert a button that will trigger the subroutine, go to Developer > Insert > Button from Form Controls > Click inside the worksheet.
 
Excel Form Controls
 

A small window will pop out and then select 'New'.
 
Excel Assign Macro
 

Copy and paste the following code in between 'Sub Button1_Click' and 'End Sub' lines.
In the following code, we defined the variables rng and cell as range. These variables will be used in the loop syntax 'For' and 'Next'. What it does is to loop for each cell in the defined range (L2 to L51 in this example), total of 50 iterations. For each iteration, it will automatically input the names one by one from the lists at column L to cell E9, and then it will print the certificate one by one.
 
 
Excel Batch Print 4
 

A new button will be created in the worksheet to print the certificates in batch. Download the example file to test it out.
 
 
 

Did you find this content useful? If yes, please consider helping us by making a Donation. You can get free pdf when you donate now, see details here.