Ask a new question

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!

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated January 15, 2024 Views 1,905 Applies to: