Batch Printing using VBA Loop
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.
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.
Insert a button that will trigger the subroutine, go to Developer > Insert > Button from Form Controls > Click inside the worksheet.
A small window will pop out and then select 'New'.
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.
Dim rng As Range, cell As Range Set rng = Range("L2:L51") For Each cell In rng Range("E9") = cell.Value ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False Next cell
A new button will be created in the worksheet to print the certificates in batch. Download the example file to test it out.
Excelcrib was founded in November 2017 by Garry, a Microsoft® Office Excel® (MS Excel) enthusiast with background in engineering. He's been using MS Excel for more than 15 years in practice with specialty in VBA.