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.
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.
About Us
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.
Contact Us
Follow Us
© Copyright 2023 Excelcrib | Privacy Policy