Page Number in Excel That Updates upon printing

Question

Is there a way to put a page number in a cell that updates upon printing?

I set Row 1-5 as to repeat on every page. However, I would like the Page value in Cell F1 to update on each page upon printing. Is there a way to do this?

Thank you.

Answer 1

I think, there's no direct way to increase cell value upon printing. But you can use VBA, how to use VBA? You can search it. My question, where's the print out area? I assume the print area from the same sheet.

Here the code :

Sub forprint()

    Dim nPrint As Variant
    Dim i, n As Long
    On Error Resume Next

    n = Range("F1").Value
    nPrint = Application.InputBox("Number of Copy", "Print")
    If TypeName(nPrint) = "Boolean" Then Exit Sub
        If (nPrint = "") Or (Not IsNumeric(nPrint)) Or (nPrint < 1) Then
            MsgBox "Enter number only & minimum 1 copy", vbExclamation, "Wrong character or value!"
        Else
            Application.ScreenUpdating = False
            For i = 1 To nPrint
                ActiveSheet.Range("F1").Value = n + nPrint
                Sheets("Sheet1").PrintOut '<< change Sheet1 to your sheet with the printarea
            Next
        Application.ScreenUpdating = True
    End If

End Sub

Insert Module at VBA window. Click Insert >> Module then paste the code in the Module window, then press F5 to run the code.