How to add page numbers to all sheets using VBA?

To add page numbers to all sheets in an Excel workbook using VBA, you can create a macro that iterates through each worksheet and sets the page number configuration. Below is a step-by-step guide and sample code to help you achieve this:

Sub AddPageNumbersToAllSheets()
    Dim ws As Worksheet
    Dim pageNum As Integer

    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Worksheets
        With ws.PageSetup
            ' Set the center footer to show page numbers
            .CenterFooter = "Page &P of &N"
        End With
    Next ws

    MsgBox "Page numbers added to all sheets.", vbInformation
End Sub

Press `ALT` + `F11` to open the Visual Basic for Applications (VBA) editor in Excel.
Click `Insert` > `Module` to add a new module where you can write your macro.
In the module window, you can write the following VBA code:
Press `CTRL` + `S` to save your work.
Close the VBA editor.
To run the macro, go back to Excel, press `ALT` + `F8`, select `AddPageNumbersToAllSheets`, and click `Run`.

Explanation of the Code:

Loop Through Worksheets: The code uses a `For Each` loop to go through each worksheet in the workbook.
Set Page Setup: For each worksheet, the `PageSetup` object is accessed, and the `CenterFooter` property is set to contain the text “Page &P of &N”, where `&P` is the placeholder for the current page number, and `&N` is the placeholder for the total number of pages.
Display Message: An optional message box is added at the end to inform you that the operation is complete.

This macro will add page numbers to the center footer of all sheets, displaying them in the format “Page X of Y”. You can modify this code to place the page numbers in other locations (left or right footer/header) by changing `.CenterFooter` to `.LeftFooter`, `.RightFooter`, `.CenterHeader`, etc.

facebooklinkedin