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
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.
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.
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
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.
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.