Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace in Headers
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! -- MB |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace in Headers
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 -- Kevin Backmann "MB" wrote: 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! -- MB |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace in Headers
Thanks for answering me, Kevin. Your macro did work, however it added 2008
instead of replacing the year. -- MB "Kevin B" wrote: 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 -- Kevin Backmann "MB" wrote: 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! -- MB |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace in Headers
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 Backmann "MB" wrote: Thanks for answering me, Kevin. Your macro did work, however it added 2008 instead of replacing the year. -- MB "Kevin B" wrote: 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 -- Kevin Backmann "MB" wrote: 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! -- MB |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace in Headers
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 -- Kevin Backmann "MB" wrote: Thanks for answering me, Kevin. Your macro did work, however it added 2008 instead of replacing the year. -- MB "Kevin B" wrote: 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 -- Kevin Backmann "MB" wrote: 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! -- MB |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace in Headers
Wow! Thanks, Kevin! I much prefer the header referencing the tab name.
Awesome! -- MB "Kevin B" wrote: 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 -- Kevin Backmann "MB" wrote: Thanks for answering me, Kevin. Your macro did work, however it added 2008 instead of replacing the year. -- MB "Kevin B" wrote: 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 -- Kevin Backmann "MB" wrote: 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! -- MB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Can the find/replace function be used for worksheet headers/footer | Excel Worksheet Functions | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |