Find - Replace...for worksheets

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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,
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top