How to Insert Page Number Using VBA in Excel: 3 Macros

Method 1 – Insert a Page Number in a Footer Using Excel VBA

Steps:

Go to Insert, choose Text, and select Header & Footer.

Insert Page Number in Footer Using Excel VBA

Scroll down and see that the footer is added.

Insert Page Number in Footer Using Excel VBA

Press Alt + F11 to open the VBA window.
Go to Insert and select Module.

Insert Page Number in Footer Using Excel VBA

Insert the following code:

Sub Page_Numbers_inFooter()
Dim Wsheet As Worksheet
Set Wsheet = Worksheets("Page Number in Footer")
With Wsheet.PageSetup
.CenterFooter = "&P"
End With
End Sub

Go back to your sheet.

Insert Page Number in Footer Using Excel VBA

Code Breakdown:

We created a Sub procedure Page_Numbers_inFooter.
We declared a variable Wsheet as Worksheet.
We used the Set and With statement to insert the page number in the center of the footer.

Go to Developer and select Macros.

Select the specified Macro name and press Run.

The macro has inserted the page number in the footer.

Method 2 – VBA to Insert the Total Page Number in the Footer

Steps:

Follow the first 3 steps from the first section.
Insert the following code in the module.

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

Go back to your sheet.

VBA to Insert Total Page Number in Footer

Code Breakdown:

We created a Sub procedure Total_Page_Number_Footer.
We used the Set and With statement to insert the total page number in the center of the footer.

Open the macro dialog box.
Select the specified macro name and just press Run.

VBA to Insert Total Page Number in Footer

The page number and the total page number are inserted into the center of the footer.

Method 3 – Insert the Page Number in a Selected Cell

We’ll insert the page number in Cell D13.

Insert Page Number in Selected Cell

Steps:

Open a new VBA module and insert the following code:

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

Go back to the sheet.

Insert Page Number in Selected Cell

Code Breakdown:

We created a Sub procedure- Page_Number_Selected_Cell.
We used the If and Else statements to count the page breaks.
We used the For loop and If statement to count and increase the page number one by one.

Select the cell where you want to insert the page number.
Follow the 5th step from the first section to open the Macro dialog box.
Select the mentioned macro name and press Run.

The macro has inserted the page number in the specific cell and shows the total page numbers, too.

Download the Practice Workbook

Insert Page Number Using VBA.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

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

14 Comments

Patrick Mar 29, 2023 at 5:29 PM

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.

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

Regards
Lutfor Rahman Shimanto (ExcelDemy Team)

Suerina Juilina May 26, 2023 at 3:29 PM

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!

Al Ikram Amit May 30, 2023 at 2:35 PM

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:

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

Aidan Jun 6, 2023 at 7:00 PM

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
- Reply
  Nujat Tasnim Jun 12, 2023 at 3:45 PM
  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.
Bart Jul 26, 2023 at 2:24 PM

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 🙂
- Reply
  Hassan Shuvo Jul 27, 2023 at 1:26 PM
  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.
  
  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:
  
  Hopefully, this will solve your problem.
  
  Regards
  Hassan Shuvo || Exceldemy
Bart Jul 27, 2023 at 9:56 PM

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

Best regards,

Bart
- Reply
  Shamima Sultana Jul 30, 2023 at 8:19 AM
  
  Dear Bart,
  
  You are most welcome. Sure, you can inform us.
  
  Regards
  ExcelDemy

Bart Jul 30, 2023 at 9:00 PM

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 🙂

Shamima Sultana Aug 1, 2023 at 9:11 AM

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.

Regards
Alif Bin Hussain

Jerry Jul 19, 2024 at 1:37 AM

Using Method 3- The Cell is in a Header, The Code outputs Page 1 of 3 on all 3 pages.
It is not incrementing.
- Reply
  Shamima Sultana Jul 24, 2024 at 11:39 PM
  
  Hello Jerry,
  
  Method-3 is working perfectly. To get the page number in a proper way you need to insert the page number first. Follow the given steps to insert the page number in a selected cell.
  First select a cell >> go to Developer tab >> from Macros >> select Page_Number_Selected_Cell().
  It will insert the page number.
  Remember to select the cell sequentially to add page numbers.
  
  Output:
  
  Regards
  ExcelDemy

How to Insert Page Number Using VBA in Excel: 3 Macros

Method 1 – Insert a Page Number in a Footer Using Excel VBA

Method 2 – VBA to Insert the Total Page Number in the Footer

Method 3 – Insert the Page Number in a Selected Cell

Related Articles

Leave a reply Cancel reply