PDA

View Full Version : Find and replace tab names



acruthi
12-07-2010, 10:55 AM
I have about 40 tabs and want to do a find and replace way to rename the tabs.

Eg:
Tab 1 = London Market Profile
Tab 2 = London Market Profile Detail

Change it to
Tab 1 = London Service Profile
Tab 2 = London Service Profile Detail

How can I get a macro to just find and replace Market to Service on the tab names for 40 tabs?

Thanks much!

p45cal
12-07-2010, 11:09 AM
Sub blah()
For Each Sht In ThisWorkbook.Sheets
Sht.Name = Replace(Sht.Name, " Market ", " Service ")
Next Sht
End Sub

Simon Lloyd
12-07-2010, 11:11 AM
try running this:
Sub change_sheet_name()
Dim Sh As Worksheet
For Each Sh In Sheets
Sh.Name = Application.WorksheetFunction.Substitute(Sh.Name, "Market", "Service")
Next Sh
End Sub

Simon Lloyd
12-07-2010, 11:11 AM
@p45cal Lol! :)

acruthi
12-07-2010, 11:40 AM
Thanks Simon and P45Cal...this was super easy!