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.


Insert Page Number in Excel Using VBA: 3 Macros

Let’s get introduced to our dataset first which 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 declare 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, 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 declare a variable, Wsheet as Worksheet.
  • Finally, use 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 


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. Using this macro, we can insert the page number in a specific cell. It will apply the page number in the active sheet. Here 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 the If and Else statements to count the page breaks.
  • Next, use the 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 shows the total page numbers too.

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


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Page Number  | Page Setup | Print in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

12 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 Lutfor Rahman Shimanto
      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!

    • Hello SUERINA JUILINA,

      Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

      There are lackings in describing your particular problem. However here is the VBA code that probably can solve your particular problem:

      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   
          Dim totalPages As Integer
          totalPages = 2
          ActiveCell.Value = "Page " & mNumPage & " of " & totalPages
      End Sub

      Finally, “Page 2 of 2” is shown as a Page Number like in the following image:

      I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

      Regards

      Al Ikram Amit

      Team ExcelDemy

  3. Hi,
    I was wondering if it is possible to alter this macro to work on cells that are part of repeating rows. i.e. are at the top of each page when printing?

    Thanks,
    Aidan

    • Hi AIDAN,
      Thank you so much for sharing your valuable feedback. We appreciate your participation and are available to assist. I assume you wish to display page numbers at the top of your Excel spreadsheet. The following VBA code may solve your particular problem:

      1. First, follow the first 3 steps from Method 1.
      2. After that, type the following code in the module.

      Sub Page_Numbers_inHeader()
          Dim Wsheet As Worksheet
          Set Wsheet = Worksheets("Page Number in Header")    
          With Wsheet.PageSetup
              .CenterHeader = "&P"
          End With
      End Sub

      3. Now open the Macro dialog box: Developer > Macros.
      4. Select the specified Macro name which is Page_Numbers_inHeader and press Run.
      5. You will see the macro has inserted the page number in the header.

      Hopefully, you will be able to solve your problem now. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.

      Regards
      Nujat Tasnim
      Exceldemy.

  4. Hi, This is an interesting topic, however not 100% what I was looking for.

    I have a excel workbook for making reports.
    these reports have to be introduced in pdf on a platform of our authorities together with a pdf with index of all the reports and the N° of pages/report

    1 report/worksheet. number of pages per worksheet can change following the content.

    My idea is to count the pages/report automaticaly in an active cell of the report, that is also linked to the index.

    So when a report is 5 pages this also is informed in the worksheet “index”

    the vba module on top of this page is working fine, but I have to activate it manually

    Best regards

    Bart
    PS I’m not a VBA expert 🙂

    • Hello Bart,
      Thank you for reaching out. Your problem has a very simple solution. Instead of writing the code in a Sub Procedure, create an Event, Worksheet_SelectionChange in each of your Worksheet and simply paste your code.
      VBA Code for Event
      Do the same thing in every worksheet except the Index worksheet and modify the code as per your requirements. In this way, you don’t have to run the code every time.
      Here’s the modified code:

       Private Sub Worksheet_SelectionChange(ByVal Target As Range)
          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
          ThisWorkbook.Worksheets("Worksheet 1").Range("I2").Value = "Page " & mNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
          ThisWorkbook.Worksheets("Worksheet 3").Range("C5").Value = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
      End Sub

      Here is the result:
      Results of the code
      Hopefully, this will solve your problem.

      Regards
      Hassan Shuvo || Exceldemy

  5. Hi Hassan, thank you very much for the solution. I will try it out and inform you the result

    Best regards,

    Bart

  6. Hi Hassan,
    It works perfect for all except 1 worksheet where it doesn’t give the correct N° of pages.
    I have to check what could be the cause.

    Since the file is getting slower probably by running the codes when scrolling in the file,
    Is it possible to add a VB line to run this code in each worksheet only when I click the tab of the worksheet “index”.
    Again, I have no real knowledge in VBA, but I’m trying 🙂

    • Hello BART,

      It is possible to add a VB line of code to run the selection change code only when you click the tab of the worksheet called “index.” To achieve this, you can use the Worksheet_Activate event in the code module of the “index” worksheet. Here’s the code.

      Private Sub Worksheet_Activate()
          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
          
          ThisWorkbook.Worksheets("Worksheet 1").Range("I2").Value = "Page " & mNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
          ThisWorkbook.Worksheets("Worksheet 3").Range("C5").Value = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
      End Sub
      

      Regards
      Alif Bin Hussain

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo