How to Insert Page Number Using VBA in Excel (3 Macros)

Get FREE Advanced Excel Exercises with Solutions!

We can insert page numbers in Excel by applying commands, but if we use VBA, we can do it more smartly and quickly along with some customizations. This article will cover three useful VBA macros to insert page number in Excel with clear steps and illustrations.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


3 Macros to Insert Page Number Using VBA in Excel

Let’s get introduced to our dataset first that represents some salespersons’ sales in different regions. There are three pages in our sheet.


1. Insert Page Number in Footer Using Excel VBA

In our first macro, we’ll insert the page number in the center of a footer. So, we’ll have to insert Footer first otherwise this macro won’t work.

Steps:

  • Click as follows to insert a footer: Insert > Text > Header & Footer.

Insert Page Number in Footer Using Excel VBA

Then scroll down to the page and see that the footer is added.

Insert Page Number in Footer Using Excel VBA

  • Press ALT + F11 to open the VBA
  • Then click as follows to insert a new module: Insert > Module.

Insert Page Number in Footer Using Excel VBA

  • Later, type the following codes in the module-
Sub Page_Numbers_inFooter()
Dim Wsheet As Worksheet
Set Wsheet = Worksheets("Page Number in Footer")
With Wsheet.PageSetup
.CenterFooter = "&P"
End With
End Sub
  • After that, go back to your sheet.

Insert Page Number in Footer Using Excel VBA

Code Breakdown:

  • First, I created a Sub procedure- Page_Numbers_inFooter.
  • Then declared a variable, Wsheet as Worksheet.
  • Later, used the Set and With statement to insert the page number in the center of the footer.

  • Next, click as follows to open the Macro dialog box: Developer > Macros.

  • Select the specified Macro name and press Run.

Now see, the macro has inserted the page number in the footer.

Read More: How to Insert Sequential Page Numbers Across Worksheets


2. VBA to Insert Total Page Number in Footer

The previous macro will only insert the page number, and won’t show the page number out of the total number. This macro will show it in the center of the footer of your excel sheet.

Steps:

Sub Total_Page_Number_Footer()
Dim Wsheet As Worksheet
Set Wsheet = Worksheets("Total Page Number in Footer")
With Wsheet.PageSetup
.CenterFooter = "&P/&N"
End With
End Sub
  • Next, go back to your sheet.

VBA to Insert Total Page Number in Footer

Code Breakdown:

  • Here, I created a Sub procedure- Total_Page_Number_Footer.
  • Then declared a variable, Wsheet as Worksheet.
  • Finally, used the Set and With statement to insert the total page number in the center of the footer.

VBA to Insert Total Page Number in Footer

Now have a look, the page number and the total page number are inserted into the center of the footer.

Read More: How to Use Formula for Page Number in Excel (6 Quick Ways)


3. Insert Page Number in Selected Cell

The previous macros would need a footer so we couldn’t insert page numbers in any of our desired locations. By using this macro, we can insert the page number in a specific cell. It will apply the page number in the active sheet. Here well insert the page number in Cell D13.

Insert Page Number in Selected Cell

Steps:

Sub Page_Number_Selected_Cell()
Dim mVCount As Integer
Dim mHCount As Integer
Dim mVBreak As VPageBreak
Dim mHBreak As HPageBreak
Dim mNumPage As Integer
mHCount = 1
mVCount = 1
If ActiveSheet.PageSetup.Order = xlDownThenOver Then
mHCount = ActiveSheet.HPageBreaks.Count + 1
Else
mVCount = ActiveSheet.VPageBreaks.Count + 1
End If
mNumPage = 1
For Each mVBreak In ActiveSheet.VPageBreaks
If mVBreak.Location.Column > ActiveCell.Column Then Exit For
mNumPage = mNumPage + mHCount
Next
For Each mHBreak In ActiveSheet.HPageBreaks
If mHBreak.Location.Row > ActiveCell.Row Then Exit For
mNumPage = mNumPage + mVCount
Next
ActiveCell = "Page " & mNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub
  • Later, go back to the sheet.

Insert Page Number in Selected Cell

Code Breakdown:

  • First, I created a Sub procedure- Page_Number_Selected_Cell.
  • Then declared some variables, mVCount and mHCount As Integer, mVBreak As VPageBreak,mHBreak As HPageBreak, mNumPage As Integer.
  • Later, I used If and Else statements to count the page breaks.
  • Next, used For loop and If statement to count and increase the page number one by one.

Soon after, you will see that the macro has inserted the page number in the specific cell and is showing the total page numbers too.

Read More: How to Insert Page Number in Excel Cell Not in Header


Conclusion

That’s all for the article. I hope the procedures described above will be good enough to insert page numbers in Excel using VBA. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.


Related Articles

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

3 Comments
  1. Hi,
    When I follow these steps I get a
    “Run-time error ‘9’:
    Subscript out of range
    Error on the “For Each mHBreak In ActiveSheet.HPageBreaks” line.

    Do you have any idea what this could be?
    Regards,
    Patrick.

    • Reply Avatar photo
      Lutfor Rahman Shimanto Mar 30, 2023 at 1:26 PM

      Hello Patrick,

      Thank you for reaching out with your issue. You encountered a Run-time error. The “Run-time error ‘9’: Subscript out of range” error occurs because you did not modify the VBA code and forgot to insert the intended sheet name. You are misled somehow by the first step of the third method. The post says to go to the Insert tab and choose Headers and Footer from the Text group, and then execute the code mentioned in the third method; that’s all.

      I can provide three Excel VBA codes to avoid the error you experienced unintentionally. All you need to do is choose the intended sheet as the active sheet and run a sub-procedure from the below list.

      First Method: Insert Page Number in Footer Using Excel VBA

      Sub AddPageNumberInFooter()
      
          ActiveWindow.View = xlPageLayoutView
          ActiveSheet.PageSetup.CenterFooter = "Page &P"
      
      End Sub

      Second Method: VBA to Insert Total Page Number in Footer

      Sub AddPageNumberWithTotalInFooter()
      
          ActiveWindow.View = xlPageLayoutView
          ActiveSheet.PageSetup.CenterFooter = "&P / &N"
      
      End Sub

      Third Method: Insert Page Number in Selected Cell

      Sub PageNumberSelectedCell()
          
          Dim mVCount As Integer
          Dim mHCount As Integer
          Dim mVBreak As VPageBreak
          Dim mHBreak As HPageBreak
          Dim mNumPage As Integer
          
          mHCount = 1
          mVCount = 1
          
          ActiveWindow.View = xlPageLayoutView
          
          If ActiveSheet.PageSetup.Order = xlDownThenOver Then
              mHCount = ActiveSheet.HPageBreaks.Count + 1
          Else
              mVCount = ActiveSheet.VPageBreaks.Count + 1
          End If
          
          mNumPage = 1
          
          For Each mVBreak In ActiveSheet.VPageBreaks
              If mVBreak.Location.Column > ActiveCell.Column Then Exit For
              mNumPage = mNumPage + mHCount
          Next
          
          For Each mHBreak In ActiveSheet.HPageBreaks
              If mHBreak.Location.Row > ActiveCell.Row Then Exit For
              mNumPage = mNumPage + mVCount
          Next
          
          ActiveCell = "Page " & mNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
      
      End Sub

      Regards
      Lutfor Rahman Shimanto (ExcelDemy Team)

  2. hi, is it possible print titles function show different page numbers?
    using script given [insert page number in a cell], my output still shows “1 of 2” at the second page.
    appreciate your reply.
    thank you!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo