Com alguma ajuda da resposta de Scott Harris, criei um botão na primeira folha chamada "Atualizar Cabeçalhos" e, em seguida, usei as células escolhidas para criar texto das células nomeadas e, em seguida, passei por todas as planilhas e atualizei eles também.
Sub Update_header()
Dim WS As Worksheet
Dim HeaderText As String
HeaderText = Sheet1.Range("F5").Text & " " & Sheet1.Range("C2").Text & Chr(10) & _
Sheet1.Range("E2").Text & " " & Sheet1.Range("F2").Text & Chr(10) & _
Sheet1.Range("B4").Text & " " & Sheet1.Range("C4").Text
For Each WS In Worksheets
WS.PageSetup.RightHeader = HeaderText
Next
End Sub
Quando comecei, tentei atualizar todas as planilhas na pasta de trabalho quando saí da planilha onde atualizei o texto que queria no cabeçalho. Mas o desempenho foi tão ruim, então decidi usar um botão e iniciar a atualização manualmente.