VBA & Macro Problems

Use Excel VBA to Replace Header and Footer Date Fields

Adam DavisAdam Davis Oct 28, 2026 870 views

Question details

The user needs a VBA macro to replace dynamic date fields in Excel headers and footers with static sample text while unlinking other automated fields.

How to Use Excel VBA to Replace Header and Footer Date Fields
Product
Microsoft Excel
Device & OS
not provided
Scenario
Modifying document headers and footers programmatically using VBA to finalize, sanitize, or anonymize spreadsheet reports before distribution.
Observed behavior
The goal is to programmatically find dynamic date fields within the page setup properties, replace them with specific text, and unlink other automated fields using a VBA script.
Before you start

Before running any VBA macros that modify worksheet properties, ensure you have saved a backup copy of your Excel workbook. You will also need to enable the Developer tab in your ribbon to access the Visual Basic Editor.

Solution 1Recommended

Modify Page Setup Properties via the VBA Editor

You can access the Visual Basic Editor to write or execute a macro that loops through worksheets and replaces header and footer strings.

Excel headers and footers use specific formatting codes (like &D for the current date). To replace these fields, a VBA script must access the ActiveSheet.PageSetup object and overwrite the properties such as LeftHeader, CenterHeader, RightFooter, etc., with your desired static text.

1
Open the Visual Basic Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.

2
Insert a New Module

In the top menu, click Insert and select Module to create a new blank workspace for your macro.

3
Input the VBA Script

Paste your custom VBA script into the module window. Ensure the code accesses the PageSetup properties and uses the Replace function to change date codes (&D) to your sample text.

4
Run the Macro

Press F5 or click the green Run button in the toolbar to execute the script and apply the header and footer changes to your workbook.

Modify Page Setup Properties via the VBA Editor
Advanced VBA Development: For highly specialized code, including complex field unlinking, consider consulting the Microsoft Q&A Office VBA development forum. Specialized engineers there can provide the exact script iterations tailored to complex worksheet structures.

Execute Excel VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust support for VBA macros, allowing you to run your existing Excel scripts to modify headers and footers without having to rewrite your code.

  1. 1. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
  2. 2. Access the Macro Tool: Navigate to the Tools tab on the top ribbon and click on Macros to open the Macro dialog box.
  3. 3. Select the Script: Select your header and footer replacement macro from the list of available scripts.
  4. 4. Run the VBA Code: Click Run to execute the VBA code and instantly update your document's headers and footers across the specified sheets.
Highly compatible with Microsoft Excel VBA macro scriptsLightweight and fast spreadsheet editorSupports advanced page setup formatting and printing featuresFamiliar user interface for a seamless transition
microsoft office alternative - wps office

Frequently Asked Questions

What is the VBA format code for the date in an Excel header?

In Excel VBA, the dynamic date code for headers and footers is represented by the string "&D". To remove the dynamic date and replace it with static text, your VBA code should search for "&D" in the PageSetup properties and replace it with your desired text string.

Why is my Excel macro not updating the footer text on all pages?

This usually happens if the workbook is protected or if the macro does not loop through all worksheets. Ensure your VBA script includes a 'For Each' loop (e.g., For Each ws In ThisWorkbook.Worksheets) to apply the changes across the entire file rather than just the ActiveSheet.

How do I enable the Developer tab to run VBA code?

Right-click anywhere on the main ribbon and select Customize the Ribbon. In the right-hand pane, check the box next to Developer and click OK. You can now access the Visual Basic Editor and Macro tools from the newly added Developer tab.