Below is a step-by-step guide on how to set headers in Excel using VBA so that you have text on the left, center, and right parts of the header. I’ll also include tips on getting the same results as when you record a macro, as well as common header “codes” for date, page number, file name, and worksheet name.

1. Basic VBA Header Setup

Here’s a minimal working example that puts “AAA” on the left, “BBB” in the center, and “CCC” on the right of the header. You can adapt these strings to any you wish:

vba
Copy code
Sub SetSimpleHeader()
    With ActiveSheet.PageSetup
        .LeftHeader = "AAA"
        .CenterHeader = "BBB"
        .RightHeader = "CCC"
    End With
End Sub

How to run:

1. Press Alt + F11 to open the VBA Editor in Excel.
2. Insert a module (if you don’t have one already).
3. Paste the code above.
4. Run the macro, then go to Print Preview or Page Layout view to see the header.

2. Using Excel’s Built-In Header Codes

Excel supports special “ampersand codes” in headers and footers that automatically insert dynamic information. Below are some commonly used ones:

• &D – Current date
• &T – Current time
• &P – Current page number
• &N – Total number of pages
• &Z – File path (drive and directory)
• &F – File name
• &A – Worksheet (tab) name
• &V – Workbook name without file path (in some Excel versions, used for workbook or folder name)

Example: To get a header that shows the current date on the left, the tab (worksheet) name in the center, and the page number on the right:

vba
Copy code
Sub SetDynamicHeader()
    With ActiveSheet.PageSetup
        .LeftHeader = "Date: &D"
        .CenterHeader = "Tab: &A"
        .RightHeader = "Page: &P"
    End With
End Sub

3. Why the Macro Recorder Code May Look Different

When you record a macro to set headers, Excel often captures additional PageSetup properties (margins, orientation, paper size, etc.). It might look like this:

vba
Copy code
Sub MacroRecordedHeader()
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = "AAA"
        .CenterHeader = "BBB"
        .RightHeader = "CCC"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        ' Other properties that Excel auto-includes, e.g.:
        ' .Orientation = xlPortrait
        ' .PaperSize = xlPaperA4
        ' .Zoom = 100
        ' .FitToPagesWide = 1
        ' .FitToPagesTall = 1
    End With
    Application.PrintCommunication = True
End Sub

• You don’t always need all those extra lines, but Excel includes them so the macro reproduces your exact print setup state.
• If you want an exact match, copy the entire recorded macro. Otherwise, the minimal code shown in sections above is often enough.

4. Common Pitfalls and Tips

1. Application.PrintCommunication:
   • In newer Excel versions, setting Application.PrintCommunication = False can improve performance if you’re making many PageSetup changes.
   • However, if you’re making only a small change or seeing inconsistencies, try removing or setting it to True after the update.
2. Check Your View:
   • To see headers and footers, switch to Page Layout view or Print Preview. In Normal view, you won’t see them.
3. Clearing an Existing Header:
   • If you’ve previously set a header and want to remove it, simply assign an empty string:

vba
Copy code
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""

1. Line Breaks in Headers:
   • If you want multiple lines in any one section, you can include a line feed with vbCrLf. For example:

vba
Copy code
.LeftHeader = "Line1" & vbCrLf & "Line2"

5. Putting It All Together

If your requirement is exactly:

• Left: Date
• Center: Tab Name
• Right: Page Number

…then the most straightforward code is:

vba
Copy code
Sub MyHeaderDesiredFormat()
    With ActiveSheet.PageSetup
        .LeftHeader = "Date: &D"
        .CenterHeader = "Tab Name: &A"
        .RightHeader = "Page: &P"
    End With
End Sub

Or, if you only need “AAA BBB CCC”, do:

vba
Copy code
Sub MySimpleHeader()
    With ActiveSheet.PageSetup
        .LeftHeader = "AAA"
        .CenterHeader = "BBB"
        .RightHeader = "CCC"
    End With
End Sub

Summary

• Use LeftHeader, CenterHeader, and RightHeader of ActiveSheet.PageSetup to customize headers in VBA.
• Incorporate built-in Excel codes (e.g., &D, &A, &P) for dynamic values like date, tab name, page number, etc.
• If the recorded macro looks different, it’s usually because Excel adds extra PageSetup properties. You don’t have to keep them all if you only need to set headers.
• Switch to Page Layout or Print Preview to confirm that your header is correct.

That’s it! By following these examples, you should get exactly the header layout you want in Excel via VBA.