How to Fix Excel VBA Header and Footer Page Number Codes
Question details
The user needs to correct VBA macro code that improperly places headers and footers or fails to correctly display page number codes.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating page setup and document formatting for printing using VBA macros.
- Observed behavior
- The executed VBA PageSetup code produces incorrect results, such as placing a right header as a footer, or replacing page number codes with unexpected strings instead of the actual page numbers.
Before modifying your VBA scripts, always test your existing code in Print Preview rather than fully printing to isolate whether the issue is with the script syntax or printer settings.
Use Macro Recorder and Refine Generated Code
Re-record the header and footer configuration manually to capture the correct syntax, then trim the generated macro to include only the necessary PageSetup properties.
The Excel Macro Recorder often captures an excess of default PageSetup properties that can interfere with your specific header or footer configurations. By recording the exact manual steps and subsequently deleting the unnecessary lines, you can create a clean, functional script.
It is crucial to verify the trimmed code's output in Print Preview, as some formatting codes (like '&P' for page numbers) behave differently depending on how they are assigned in the VBA module.
Navigate to the Developer tab on the ribbon and click 'Record Macro'. Give your macro a test name and click OK.
Go to Page Layout > Page Setup. In the Header/Footer tab, manually insert your desired text and page number codes (using the built-in buttons for Page Number and Total Pages).
Return to the Developer tab and click 'Stop Recording'.
Press Alt + F11 to open the VBA Editor. Locate the newly recorded module. You will see a large block of With ActiveSheet.PageSetup code. Delete all lines that are not related to your specific header or footer (keep properties like .RightHeader or .CenterFooter).
Run your modified, streamlined macro and immediately check File > Print to view the Print Preview. Confirm that the page numbers and placement match your expectations.
Set Up Headers and Footers Effortlessly in WPS Office
Avoid complicated VBA debugging by using WPS Spreadsheets' intuitive Page Setup tools, or utilize its highly compatible macro environment to automate your headers, footers, and page numbers with ease.
- 1. Open Your Workbook: Launch WPS Spreadsheets and open the document you wish to format.
- 2. Access Page Setup: Navigate to the 'Page Layout' tab and select the 'Page Setup' dialog box launcher.
- 3. Configure Visually: Switch to the 'Header/Footer' tab to visually select or customize page numbers and placements without writing any code.
- 4. Run Macros If Needed: If automation is required, press Alt + F11 to open the Macro Editor and run your standard Excel VBA scripts flawlessly within WPS.
Frequently Asked Questions
What are the correct VBA codes for inserting page numbers in headers or footers?
In VBA, use the '&P' string code to insert the current page number and '&N' for the total number of pages. An example of correct syntax is: ActiveSheet.PageSetup.CenterFooter = "Page &P of &N".
Why does my recorded VBA macro put the header in the footer position?
The macro recorder captures every setting in the PageSetup dialog. If a previous setting or default is triggered, it can generate overlapping or conflicting code. Trimming the recorded code block to only include the exact target property (e.g., .RightHeader) usually resolves misplacements.
Does VBA PageSetup code apply to all sheets automatically?
No, VBA PageSetup commands typically only affect the ActiveSheet. To apply your header and footer configurations across multiple sheets, you must loop through the desired worksheets in your VBA script using a 'For Each ws In Worksheets' loop.
