VBA & Macro Problems

How to Display Combined Page Numbers in Excel Cells Across Worksheets

John WilsonJohn Wilson Nov 2, 2026 869 views

Question details

The user needs to display a sequential, combined page number (e.g., Page 1 of 4, Page 2 of 4) directly inside specific Excel cells across a cover page and subsequent worksheets.

How to Display Combined Page Numbers in Excel Cells Across Worksheets
Product
Excel
Device & OS
not provided
Scenario
Preparing a multi-sheet workbook for printing where precise page numbering within the worksheet body cells is required, rather than using the standard header or footer.
Observed behavior
Excel lacks a built-in formula to insert total page numbers directly into cells. Standard VBA scripts only calculate page counts for the active worksheet, failing to combine totals across multiple sheets or account for repeated print-title rows.
Before you start

Ensure you have enabled the Developer tab in Excel to access the VBA Editor, and save your workbook as a Macro-Enabled Workbook (.xlsm) to preserve the custom script.

Solution 1Recommended

Use a Custom VBA Procedure for Cross-Sheet Page Numbering

Implement a VBA macro that reads print areas and page breaks across all required worksheets to calculate the combined page count and writes it to the designated cells.

Because Excel cannot natively populate cells with total print pages across an entire workbook, a custom VBA script is required. This script must loop through both the cover sheet and data sheets, counting horizontal and vertical page breaks while factoring in repeated rows (Print Titles) that alter the page flow.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.

2
Insert a New Module

In the left-hand Project Explorer pane, right-click your workbook name, select 'Insert', and click 'Module' to create a blank script window.

3
Create the Counting Procedure

Write a VBA sub-procedure that loops through the sheets array. Use the `HPageBreaks.Count` and `VPageBreaks.Count` properties of each sheet's print area to calculate the physical printed pages per sheet, adding them together for the grand total.

4
Output the String to Cells

Define the target cells in your VBA code (e.g., `Sheets("Cover").Range("A1").Value = "Page 1 of " & TotalPages`) to insert the calculated sequential strings across the cover and secondary sheets.

Use a Custom VBA Procedure for Cross-Sheet Page Numbering
Account for Print Titles: If your second sheet uses 'Print Titles' to repeat header rows on every page, ensure your VBA logic evaluates the actual data range size, as repeated rows increase the total number of horizontal page breaks.
Advanced Multi-Sheet Printing with WPS Spreadsheet

Easily Manage Page Numbers and Macros in WPS Office

WPS Spreadsheet provides excellent compatibility with Excel macros (VBA) and offers intuitive built-in header/footer tools to manage cross-sheet page numbering smoothly without complex coding when cell values are not strictly required.

  1. 1. Open your multi-sheet workbook: Launch WPS Spreadsheet and open your .xlsm or .xlsx workbook.
  2. 2. Access the VBA Editor: Navigate to the 'Tools' or 'Developer' tab and open the 'VBA Editor' to seamlessly run your cross-sheet counting macros.
  3. 3. Use built-in Header/Footer options: If you don't require the page number strictly in a cell, select all sheet tabs, go to 'Insert' > 'Header and Footer', and add the '&[Page] of &[Pages]' variable to automatically number the entire workbook.
  4. 4. Preview cross-sheet layout: Click 'Print Preview' to verify that the page sequences perfectly combine the cover sheet and subsequent data sheets before printing.
Highly compatible with Microsoft Excel formats (.xlsx, .xlsm)Supports advanced VBA macros for custom cell page numberingIntuitive Page Setup interface for multi-sheet printingFree, lightweight, and fast-loading alternative to MS Office
QA img-9

Frequently Asked Questions

Why doesn't the standard &[Page] formula work in standard Excel cells?

Excel's built-in variables like &[Page] and &[Pages] are designed strictly to be rendered by the print engine inside Headers and Footers. They cannot be evaluated inside standard worksheet formulas or cells.

Can I number pages across multiple sheets without using VBA?

Yes, but only if you use Headers or Footers instead of cells. By grouping (selecting) all the required sheet tabs at the bottom before opening Print Preview, the application will treat them as a single print job and sequence the page numbers seamlessly.

How do repeated print-title rows affect VBA page counts?

Repeated print titles consume physical space on each printed page. This reduces the number of data rows that can fit on a single page, subsequently increasing the total number of horizontal page breaks. A robust VBA script must account for these rows to calculate accurate totals.