Add Custom Header Using Macros In Excel

Ever wanted to add a header to your Excel spreadsheet? You can add a custom header using Excel Macros! Make sure your Excel has the Developer Tab enabled following this tutorial. I explain how you can do this below step by step!

Key Takeaways

• Automate Header Insertion – Macros allow you to quickly add custom headers to multiple worksheets without manually editing each one.
• Use VBA for Customization – VBA (Visual Basic for Applications) provides flexibility to insert dynamic headers, including dates, file names, and user-defined text.
• Control Header Alignment – Macros can specify header alignment (left, center, or right) for a professional and organized look.
• Apply Across Multiple Sheets – A single macro can be written to insert headers across multiple sheets, saving time and ensuring consistency.
• Easily Modify Header Content – By editing the macro code, you can update header content without manually adjusting each sheet.

Quick Overview

What does it do?

Adds a custom header with your text

Copy Source Code:

Sub AddCustomHeader()

Dim inputText As String
inputText = InputBox("Enter your text for the custom header", "Custom Header")

'Add your custom text to the center header
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = inputText
.RightHeader = ""
End With

End Sub

Final Result: 

 

 

How to Add Custom Header Using Macros In Excel

STEP 1: Go to Developer > Code > Visual Basic

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

 

STEP 3: Let us test it out!

Go to Developer > Code > Macros

Make sure your macro is selected. Click Run.

 

STEP 4: Type in your custom header, click OK.

To check if the header did get added, go to File > Print:

Now you should be able to see your text on your header!

 

Frequently Asked Questions

How do I add a custom header using a macro in Excel?

You can use VBA by opening the Visual Basic Editor (VBE) (Alt + F11), inserting a new module, and using the ActiveSheet.PageSetup.CenterHeader = "Your Header Text" command to insert a header.

Can I insert dynamic content like the file name or date in the header?

Yes, you can use VBA functions like ActiveWorkbook.Name for the file name or Format(Date, "mm/dd/yyyy") for the current date in your header.

How do I apply the same header to all sheets using a macro?

Use a loop in VBA, such as:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.CenterHeader = "Company Report"
Next ws
This ensures all sheets receive the same header.

Can I remove a header using VBA?

Yes, setting the header to an empty string removes it: ActiveSheet.PageSetup.CenterHeader = ""

Where do I save my macro so it works in any workbook?

Save it in your Personal Macro Workbook (PERSONAL.XLSB) so the macro is available across all Excel files you open.