Change Header data

How can I change the header data on all worksheets at once? Specifically, I want to change 2004 to 2005 in the header contribution reports.

Are all the page setups exactly the same?

If yes, you could group the sheets that should get changed (click on the first
worksheet tab and ctrl-click on subsequent).

Then file|page setup.

If the page setups are different, you could use a macro.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim OldYear As String
Dim NewYear As String

OldYear = "2004"
NewYear = "2005"

For Each wks In ActiveWindow.SelectedSheets
With wks.PageSetup
.RightHeader _
= Application.Substitute(.CenterHeader, OldYear, NewYear)
.CenterHeader _
= Application.Substitute(.CenterHeader, OldYear, NewYear)
.LeftHeader _
= Application.Substitute(.CenterHeader, OldYear, NewYear)
End With
Next wks
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Group the worksheets you want fixed, run the macro and ungroup those
worksheets. Remember that almost anything you do to a worksheet that's grouped
with others, you do to all the group.

And if you decide to use this same routine to change other text, you'll have to
be careful. Application.Substitute (and Replace in xl2k+) is case sensitive.



MrMountain wrote:
>
> How can I change the header data on all worksheets at once?
> Specifically, I want to change 2004 to 2005 in the header contribution
> reports.
>
> --
> MrMountain
> ------------------------------------------------------------------------
> MrMountain's Profile: http://www.excelforum.com/member.php...o&userid=30705
> View this thread: http://www.excelforum.com/showthread...hreadid=503680

--

Dave Peterson