Find-Replace in Excel Header

Question

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 1

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

Answer 2

Thank you so much, again, for your assistance.  First of all, the active sheet code now worked, so that is what I am using.  I think the count of tabs in the file is 133, with 27 needing header/footer updates because they are being used in the presentation.  Of the other sheets, some have headers becuase they used to be printed, but they havent been updated in several quarters, so the blank header if statement doesn't necessarily help. 

Anyway, if you are not sure why the first code failed, for sure I am not sure why!  In any case, with the active sheet code, and 27 sheets to update, it is less than one minute to go through them all.  It is much faster than it used to be going into the dialog each time, and at this point, at least for me, since it would only save a minute to make a code that would update all sheets at once, I'm not sure how much it is worth it to try to figure it out. 

Since I am very happy with the current improvement, I wouldn't want to trouble you with having to do more work on my behalf, since it wouldn't help all that much and I may not be able to use it anyway.

Again, I appreciate so much your assistance..  Best wishes to you!

Michael

Answer 3

Thank you so much.  I think that this is what I want, but as a beginner to using macros, I am having trouble making it work properly the way I want to.

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.  The problem is that not every sheet in my file is used in the presentation, and thus not all sheets have headers.

My footer update code, which was

Sub UpdateFooter()

   ActiveSheet.PageSetup.LeftFooter = Range("References!EV1").Value

End Sub

was only one sheet at a time, but is still significantly faster than going in to each header/footer dialog, and it was also faster than your code was taking. 

I thought that

Sub ReplaceHeaders()

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

End Sub

would work, but it did not.  Alas, I am much less than a novice at visual basic, so before I go and rearrange all headers manually, I am going to trouble you (or someone else from the community) again to provide the necessary adjustment.

Thank you so much, again!

Michael

Answer 4

It is always a good idea to post the code you are actually using so that if you have an error of logic then we can point out the error. However, the following code should do what you require.

Sub ReplaceHeaders()

Dim ws As Worksheet

For Each ws In Worksheets

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

Next ws

End Sub

You could just replace the Q4 on its own provided that same string does not appear in the XXXX part

ws.PageSetup.CenterHeader = Replace(ws.PageSetup.CenterHeader, "Q3", "Q4")

I usually use as much of the fixed part of the string as possible because that reduces the possibility of the same string being repeated in another part of the field. You can also check out the optional arguments for the Replace function in Help and you will see that you can restrict to the number of times the replacement will occur and if you know it is always the start of the string and set the number of replacements to 1 then that eliminates the possibility of replacing the string in other parts of the field.

Following code starts looking for the string at the first character and only replaces the string once in the field.

ws.PageSetup.CenterHeader = Replace(ws.PageSetup.CenterHeader, "Q3", "Q4", 1, 1)