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

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

Further Reading:

Print a worksheet or workbook