May 16, 2022

Change Values while Printing Multiple Copies

How to change values automatically while printing multiple copies using a macro. Watch the video below:

Change Values while Printing

Sometimes a user may need to print multiple copies of a worksheet and would like to have for example, different names or headers, to distribute the copies to relevant departments or persons. The VBA macro code below helps to easily automate the process:

Sub PrintCopiesWithDifferentHeaders()

Dim i As Long
Dim Plist As Variant

Plist = Array("Jack", "Jill", "Ram", "Irrfaan")

For i = LBound(Plist) To UBound(Plist)
    Range("D1") = Plist(i)
    Range("D1").Font.Bold = True
' Make gridlines visible during print preview
    With ActiveSheet.PageSetup
        .PrintGridlines = True
    End With
    'ActiveSheet.PrintOut
    ActiveSheet.printpreview
Next
Range("D1").Clear

End Sub

Change values while printing multiple copies

Further Reading:

Print a worksheet or workbook