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

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.


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.


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.


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.

Mithun

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo