Use Excel VBA to Replace Header and Footer Date Fields
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.

- 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 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.
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.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.
In the top menu, click Insert and select Module to create a new blank workspace for your macro.
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.
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.

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. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
- 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. Select the Script: Select your header and footer replacement macro from the list of available scripts.
- 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.

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.




