Find and replace in header and footer in Excel

If you want to replace text in Excel, you can go to Home >> Editing >> Find & Select >> Replace (Ctrl + H).

It’s not very complicated. The problem is that it doesn’t look for text in headers and footers, so there is no way to replace it using this method. If you want to replace the text there, you have to use the VBA code.

Insert header or footer

Let’s insert three headers to Excel (Insert >> Text >> Header & Footer).

Now, you have three headers inserted (left, center and right). You can click each of them and enter text.

If you scroll your page down, you will notice that there are also three footers there. Here, you can enter text in a similar way.

Replace headers or footers in the current sheet

The following code is going to replace text in the center header on the current worksheet. “Center header” is going to be “Center header 1”.

Sub ReplaceSingleHeader()
    mysheet = ActiveSheet.Name

    If Worksheets(mysheet).PageSetup.CenterHeader <> "" Then
        Worksheets(mysheet).PageSetup.CenterHeader = Replace(Worksheets(mysheet).PageSetup.CenterHeader, "header", "header 1")
    End If
End Sub

This is how it looks like in Excel.

In a similar way, you can do it for a footer.

Sub ReplaceSingleFooter()
    mysheet = ActiveSheet.Name

    If Worksheets(mysheet).PageSetup.CenterFooter <> "" Then
        Worksheets(mysheet).PageSetup.CenterFooter = Replace(Worksheets(mysheet).PageSetup.CenterFooter, "footer", "footer 1")
    End If
End Sub

I showed you how you can change the text for the center header. You can also do it for LeftHeader and RightHeader, or all of them at once.

Replace headers in all sheets

(Change code, the previous one worked for all headers)

Sub ReplaceAllHeaders()
    Dim i As Long
    For i = 1 To ActiveWorkbook.Sheets.Count
        With Sheets(i).PageSetup
            If Sheets(i).PageSetup.CenterHeader <> "" Then Sheets(i).PageSetup.CenterHeader = Replace(Sheets(i).PageSetup.CenterHeader, "header", "header 1")
        End With
    Next i
End Sub

So far, our code worked for a single worksheet

Adding InputBox – the easy way

In the previous example, we added two strings inside our code. The disadvantage of this is that we have to change it each time if we want to use different strings.

The other way to do our script is to add an input box.

Sub ReplaceSingleHeaderInputBox()

    Dim ws As Worksheet
    Dim first_input As Variant
    Dim second_input As Variant
    
    first_input = InputBox("Enter text to replace")
    second_input = InputBox("Enter replace phrase")
    
    For Each ws In Worksheets
        If ws.PageSetup.CenterHeader <> "" Then
            ws.PageSetup.CenterHeader = Replace(ws.PageSetup.CenterHeader, first_input, second_input)
        End If
    Next ws
End Sub

Each time you start a program, you will be asked for two strings. Each string for each input box.

The disadvantage of this method is that it’s not the most elegant way. The next example shows how to do it using a single form.

Adding InputBox – the bit harder way

In order to create this form, press Alt + F11 to open VBA editor. Choose Insert >> UserForm.

Change the name to frmReplaceHeader and caption to Replace Header.

From a Toolbox (View >> Toolbox) insert:

Two labels: lblFindWhat and lblReplaceWith.

Two textboxes: tbxFindWhat and tbxReplaceWith.

One command button: btnReplaceAll.

Change captions and place them in the same way as you can see in the image below.

Double click the “Replace All” button to generate the private procedure. The code you type there will be executed each time you click the “Replace All” button.

Private Sub btnReplaceAll_Click()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        If ws.PageSetup.CenterHeader <> "" Then
            ws.PageSetup.CenterHeader = Replace(ws.PageSetup.CenterHeader, Me.tbxFindWhat.Value, Me.tbxReplaceWith.Value)
        End If
    Next ws
End Sub

Code explanation:

In this code, in the Replace function, there is Me.tbxFindWhat.Value and Me.tbxReplaceWith.Value. Me refers to user form, then it takes value from two textboxes and the rest of the code works the same way as before.

Posted in vba