Md. Sourov Hossain Mithun
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.
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.
Then scroll down to the page and see that the footer is added.
- Press ALT + F11 to open the VBA
- Then click as follows to insert a new module: Insert > Module.
- 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.
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.
Now see, the macro has inserted the page number in the footer.
Read More: How to Insert Sequential Page Numbers Across Worksheets
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:
- Follow the first 3 steps from the first section.
- Then write the following codes in it
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.
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.
- Follow the 5th step from the first section to open the macro dialog box.
- Select the specified macro name and just press Run.
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.
Steps:
- Follow the first 3 steps from the first section.
- After that, type the following codes in the module-
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.
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.
- After that, 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 finally, just press Run.
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.
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.
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
Second Method: VBA to Insert Total Page Number in Footer
Third Method: Insert Page Number in Selected Cell
Regards
Lutfor Rahman Shimanto (ExcelDemy Team)
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!