Find-Replace in Excel Header

I found the assistance at http://support.microsoft.com/kb/273028 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.

 

Thanks!

 

Question Info


Last updated May 15, 2019 Views 1,255 Applies to: Have the experts at Excelchat fix your formulas for free. See how.
Answer
Answer

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")





Regards,

OssieMac

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.