Find & Replace in Headers

[MB]

My workbook has a 12 worksheets - 1 for each month. The header for each
worksheet has the month and year. How do I find and replace only the year?
I selected all tabs and then changed the year, but it changed the entire
header.

Working with 2007.

Thank you!

 

[Kevin B]

The following macro should do the trick. Press Alt + F11 to open the VB
Editor and click INSERT on the menu and select module. Copy or type the
following code:

Sub ModifyHeader()

Dim varYear As Variant
Dim ws As Worksheet
Dim wsStart As Worksheet

varYear = InputBox("Enter the year to put into the " & _
"header: ", "Enter 4 Digit Year", Year(Date))

With Application
.ScreenUpdating = False
.StatusBar = "Currently busy, please wait..."
End With

Set wsStart = ThisWorkbook.ActiveSheet

If Len(Trim(varYear)) = 0 Then Exit Sub

If Not IsNumeric(varYear) Then
MsgBox varYear & " doesn't appear to be a valid year."
ModifyHeader
End If

For Each ws In ThisWorkbook.Sheets
With ws
.Activate
.PageSetup.CenterHeader = "&A " & varYear
End With
Next ws

wsStart.Activate
Application.StatusBar = False
Set wsStart = Nothing
Set ws = Nothing

End Sub


Hope this helps

 

[MB]

Thanks for answering me, Kevin. Your macro did work, however it added 2008
instead of replacing the year.

 

[Kevin B]

Let me ask you this then? Do you have the month/year as the worksheets tab
name. If the name format is January 2007 or Jan 2007, renaming the tab names
is what needs to be done. Hold on and I'll get you a macro to do just that
in a minute or so.

 

[Kevin B]

Here's a variation of the original, only this one changes the tab names of
the worksheet to Month Year, and has the header reference the tab name.

Create the macro the same way the other one was created.

Here's the code:


Sub ModifyTab()

Dim varYear As Variant
Dim ws As Worksheet
Dim wsStart As Worksheet
Dim strTab As String

varYear = InputBox("Enter the year to put into the " & _
"header: ", "Enter 4 Digit Year", Year(Date))

With Application
.ScreenUpdating = False
.StatusBar = "Currently busy, please wait..."
End With

Set wsStart = ThisWorkbook.ActiveSheet

If Len(Trim(varYear)) = 0 Then Exit Sub

If Not IsNumeric(varYear) Then
MsgBox varYear & " doesn't appear to be a valid year."
ModifyHeader
End If

For Each ws In ThisWorkbook.Sheets
strTab = ws.Name
strTab = Left(strTab, Len(strTab) - 4) & varYear
ws.Name = strTab
With ws
.Activate
.PageSetup.CenterHeader = "&A "
End With
Next ws

wsStart.Activate
Application.StatusBar = False
Set wsStart = Nothing
Set ws = Nothing


End Sub

 

[MB]

Wow! Thanks, Kevin! I much prefer the header referencing the tab name.
Awesome!