Find-Replace in Excel Header

I found the assistance at very helpful.  I was able to use it to update my date footer, running it for each sheet without tediously having to go into the page setup for each one. for multiple sheets very quickly.  But for my header, it does not work because it changes the entire center header, which is different for each sheet.  Specifically, my center header is currently Q3 2013 XXXX, where XXXX is different for each sheet.  I would like to change it to Q4 2013 XXXX, simply replacing Q3 with Q4, without having to tediously go o the page setup for each tab.




I tried your code exactly, it was processing for a very long time, much longer than I had expected, so I force-closed excel and started over


Hello Michael,

If you had to force close Excel then I believe that something else is wrong. Even with a vast number of worksheets I should think that it will run in the blink of an eye. However, please tell how many worksheets you have and I will create a workbook with that number of sheets and tell you how long it takes to run.

The following code will test for worksheets without headers and skip them but the code should not really require the test because the Replace function simply ignores the zero length string.  I have even tested the code after setting bold print for some worksheets and it still works.

Sub ReplaceHeaders()

    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.PageSetup.CenterHeader <> "" Then
            ws.PageSetup.CenterHeader = Replace(ws.PageSetup.CenterHeader, "Q3 2013", "Q4 2013")
        End If
    Next ws

End Sub

Your posted code for ActiveSheet is missing ActiveSheet before PageSetup. Should be like the following.

 ActiveSheet.PageSetup.CenterHeader = Replace(ActiveSheet.PageSetup.CenterHeader, "Q3 2013", "Q4 2013")



Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


Question Info

Last updated April 3, 2020 Views 1,388 Applies to: