Find and Replace Across Specific Sheets based on a Data set with correct names

[SamNew2Coding]

I have a list of correct and incorrect names as seen in sample file below. I am trying to loop through a list of sheets and do a find and replace for each item on the List. The Correct Name is what the replace is going to be and the Incorrect Name is the find. I have looped through specific sheets using arrays before but i cant figure out how to incorporate cell values from a variable sized list in the middle of the loop.

In the end, i just want to do a find and replace for a list of find and replacement data and perform that replacement in just select sheets. The issue is the amount of find and replaces will vary depending om how long the replacement list is.

Book1
	A	B
1	Incorrect Name	Correct Name
2	Item100	Pencil
3	Item101	Pen
4	Item102	Paper
5	Item103	Staple
6	Item104	Marker
Sheet1

I have started with code i found someone else upload to a forum and started adding a value lookup for the first row of data but stuck here.

Dim ws As Worksheet
Dim stFind As String, stReplace As String

stFind = Sheets("Data").Range("A2").Value
stReplace = Sheets("Data").Range("B2").Value

For Each ws In ThisWorkbook.Worksheets

Select Case ws.Name
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"

ws.Cells.Replace What:=stFind, Replacement:=stReplace, LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Case Else
End Select
Next ws

 

[Fluff]

How about

Sub Samnew2Coding()
   Dim Ary As Variant
   Dim i As Long
   Dim Ws As Worksheet
   
   With Sheets("Data")
      Ary = .Range("A2", .Range("B" & Rows.Count).End(xlUp))
   End With
   For Each Ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
      For i = 1 To UBound(Ary)
         Ws.UsedRange.Replace Ary(i, 1), Ary(i, 2), xlWhole, , False, , False, False
      Next i
   Next Ws
End Sub

 

[SamNew2Coding]

That is almost exactly what i was looking to do. It appears that it changed that in more than just the selected sheets however.

 

[Fluff]

That suggests that you had previously done a manual search or replace & changed "Within" for Sheet to Workbook.
Does that ring a bell?

 

[SamNew2Coding]

Yes, i have probably done manual searches in this workbook where the setting is to look at the entire workbook. Is there a way to specify to look in the individual sheet in that code or should anyone who uses the file have to make sure that they manually adjust the find setting prior to executing?

 

[Fluff]

Ok, try

Sub Samnew2Coding()
   Dim Ary As Variant
   Dim i As Long
   Dim Ws As Worksheet
   
   With Sheets("Data")
      .Range("A1").Find ("*")
      Ary = .Range("A2", .Range("B" & Rows.Count).End(xlUp))
   End With
   For Each Ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
      For i = 1 To UBound(Ary)
         Ws.UsedRange.Replace Ary(i, 1), Ary(i, 2), xlWhole, , False, , False, False
      Next i
   Next Ws
End Sub

 

[SamNew2Coding]

I have no clue how that resets things but that seemed to have worked wonders! Thanks so much for teaching me something new!

 

[Fluff]

As you cannot search or replace across the workbook in VBA, as soon as you use either Find or Replace, it resets the "within" setting back to sheet.