Find - Replace...for worksheets

[Ian Mac]

is there a way to Find Replace worksheet names using VB?
i.e. I have 7 sheets called AgentOld + Day of the week. agent1 leaves and a new person joins th team I'd like a quick way to Find AgentNew and relace with AgentNew

Thanks in advance,

 

[Dave Hawley]

Hi Ian

With only 7 sheets why not just rename in manually?

 

[Ian Mac]

Hi dave,

I would BUT 7 sheets for 1 person, 15 people per team, 35 teams on site.
They all constantly change. I was hoping for a piece of code that would help me with this ardious task.

Thanks

 

[Jay Petrulis]

On 2002-04-08 08:55, Ian Mac wrote:
Hi dave,

I would BUT 7 sheets for 1 person, 15 people per team, 35 teams on site.
They all constantly change. I was hoping for a piece of code that would help me with this ardious task.

Thanks

Hi,


Try something like the following. I assume that you have a way to identify the AgentOld and AgentNew strings already in place.

Change the ThisWorkbook reference to the worksheet(s) to cycle through workbooks (can be done in code as well).

---------------------
Sub test()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Activate
Cells.Replace What:=AgentOld, Replacement:=AgentNew, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next ws

End Sub
--------------------------

HTH,
Jay

 

[Dave Hawley]

Hi Ian

Ok, now I understand!

The easiest way would be via a very simple UserForm that could be activte via a Button. I have such a form with code that you could use just email me if you are interested and I will send you a copy.