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.
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, _
Next cell
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.
Show Comments

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.

© Copyright 2021 Excelcrib | Privacy Policy

We use cookies to improve your browsing experience.
Continuing to use this site means you agree to our use of cookies.
Tell me more!