Why is the page header not saved when I save and close a workbook?

Question

Using a macro, I have a workbook that I want to open if not already open, add/modify the page header, then save when closing it.

Everything appears to work until the save and close part. The page header is there when I check right before saving the workbook. After closing and saving changes, I open the workbook tgo find the header is missing.

I use a network drive so I tried using the C: drive instead just in case it was a network issue (an extreme long shot but I'm searching for any cause). I tried saving the workbook, re-opening it, changing the page header and then re-saving it. I've tried renaming the workbook when saving. I do need to use a variable for the header since it will be changing when I run it. I set it to "Saturday" here in order to keep the code as simple as necessary.

Each time the header would disappear when I re-open the workbook. This all works if I do it manually, but not when using a macro to do it.

    Option Explicit
    Sub Header_Test()

    Dim WB As Workbook
    Dim wPathName, wBookName, wExt, wFullPathName As String
    Dim LHeader As String
    
    wPathName = "C:\Temp\"
    wBookName = "Header Test"
    wExt = ".xlsx"
    wFullPathName = wPathName & wBookName & wExt
    LHeader = "Saturday"
    
    'Open workbook (if not open) and make it active.
    For Each WB In Workbooks
        If WB.Name = wBookName Then 'If Header Test workbook is open then make it active.
            WB.Activate
        Else
            Workbooks.Open (wFullPathName) 'Workbook isn't open so open it.
        End If
    Next WB
    
    'Add left header.
    Application.PrintCommunication = False
    With Sheets("Sheet1").PageSetup
        .LeftHeader = "&24 " & LHeader
        .HeaderMargin = Application.InchesToPoints(0.3)
        .Orientation = xlPortrait
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    'Save and close
    Workbooks(wBookName).Close savechanges:=True
    
    End Sub

Answer 1

Not sure if the header is the correct way you wanted but here's the result on my end with the edited code:

Sub Header_Test()
    Dim WB As Workbook, LHeader As String
    Dim wPathName, wBookName, wExt, wFullPathName As String
    
    wPathName = "Q:\Aankoop\Werkfiles Excel\Steven\Test\"
    wBookName = "Header Test"
    wExt = ".xlsx"
    wFullPathName = wPathName & wBookName & wExt
    LHeader = "Saturday"
    
    'Open workbook (if not open) and make it active.
    Set WB = Workbooks.Open(wFullPathName)
    'WB.Activate
    'Add left header.
    Application.PrintCommunication = False
    With WB.Sheets("Sheet1").PageSetup
        .LeftHeader = "&24 " & LHeader
        .HeaderMargin = Application.InchesToPoints(0.3)
        .Orientation = xlPortrait
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    Application.PrintCommunication = True 'See (Edit:) below code
    'Save and close
    WB.Save
    WB.Close

End Sub

(Didn't see BigBen's last comment on your question so this is a bit redundant) I didn't understand why you didn't just use Workbooks.Open(wFullPathName) to be honest, it's the easiest way to make sure that when the filepath exists, that it's opened because it doesn't care that it's already opened.
The WB.Activate I felt was unnecessary as well as long as you fully declare the sheet, i.e. WB.Sheets("Sheet1")
Last pointer, why use Workbooks(wBookName).Close when you already have a workbook variable WB?

(Edit:) Can't really call this an edit since I haven't posted my answer yet but I started testing a bit more and stumbled upon your same issue @OP. The code worked when going step by step through the code, running it with F5 however, didn't change the file even when I tried changing a range and changing it back to original value after a save and then saving again. Nor did Application.Wait help. Then I looked at the documentation and noticed them using Application.PrintCommunication = True for the following
Set the PrintCommunication property to True after setting properties to commit all cached PageSetup commands.
Before you ask, I have no idea why going step by step does this automatically and when running a code you need the extra line..

Answer 2

• I've tested the code on M365 w/o issue with header setting

• The code to check the opened workbook has a bug. If the workbook (Header Test) is opened, but it isn't the first workbook. WB.Name = wBookName is evaluated as False, then your code Else: Workbooks.Open (wFullPathName) tries to open it again.

Pls try.

    Dim bExist As Boolean
    For Each WB In Workbooks
        If WB.Name = wBookName Then 'If Header Test workbook is open then make it active.
            WB.Activate
            bExist = True
            Exit For
        End If
    Next WB
    If Not bExist Then
        Workbooks.Open (wFullPathName) 'Workbook isn't open so open it.
    End If