Replace text on header and footer of Excel file

Question

I want to check the header and footer on a Excel sheet, and replace all the ocurrences of a given string by another string. How can this be done using vba?

Answer 1

You will need to use to use Sheet.PageSetup property. I am assuming you are looking for center header and footer. The following will work for you

Sub LoopThroughPageSetup()
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        If sh.PageSetup.CenterHeader = "hello" Then 'change to whatever you want
            sh.PageSetup.CenterHeader = "hi"
        End If
        If sh.PageSetup.CenterFooter = "hi" Then
            sh.PageSetup.CenterFooter = "hello"
        End If
    Next sh
End Sub