Find and Replace Macro to Omit a Specified Worksheet

[matratus34]

Hi - I've got a workbook with around 50 data tabs which are updated monthly and I need to do a find and replace on around 40 different values each time the data is refreshed. (the SPV values)

e.g
find ABC replace with XYZ
find DER replace with NG1
find FOR replace with ZG1
x40

I've got a tab with all my old and new values stored ("SPVList")

I've written a macro that will loop down my old and new values and find and replace on each tab but this also amends the values on my "SPVList" tab which I need to keep intact for the next time I run the macro.

Any help with the code so that it changes the values on all tabs apart from the "SPVList" tab would be greatly appreciated.

Sub SPV_Changes()


'Changes SPV code to parent DOH code

Worksheets("SPVList").Select

'Ask if user want to change the DOH codes - if not exit macro

chgSPV = MsgBox("Do you want to amend SPV Codes?", vbYesNo)

If chgSPV = vbNo Then Exit Sub

'Loop through rows containing SPV codes and replace with parent codes

i = 6

Do While Not IsEmpty(Cells(i, 2))


Selection.Replace What:=Cells(i, 2).Value, Replacement:=Cells(i, 3).Value, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

i = i + 1

Loop

End Sub


Thanks Guys

 

[Fluff]

How about

Sub matratus()
   Dim Ary As Variant
   Dim Ws As Worksheet
   Dim i As Long
   
   With Sheets("SPVlist")
      Ary = .Range("B6:C" & .Range("B" & Rows.Count).End(xlUp).Row)
   End With
   For Each Ws In WorkSheets
      If Not Ws.Name = "SPVList" Then
         For i = 0 To UBound(Ary)
            Ws.UsedRange.Replace Ary(i, 1), Ary(i, 2), xlWhole, , False, , False, False
         Next i
      End If
   Next Ws
End Sub

 

[matratus34]

Hi Fluff - that's brilliant - Thank you so much.

Just one other thing - if there was more than one worksheet that I didn't want to amend how would I do that?

Would it be amending this line?

If Not Ws.Name = "SPVList" Then

would I just need to add an OR?

For example:

If Not Ws.Name = "SPVList" or "Sheet2" or "Sheet3" Then


Thanks again

 

[Fluff]

To ignore multiple sheets use

Sub matratus()
   Dim Ary As Variant
   Dim Ws As Worksheet
   Dim i As Long
   
   With Sheets("SPVlist")
      Ary = .Range("B6:C" & .Range("B" & Rows.Count).End(xlUp).Row)
   End With
   For Each Ws In WorkSheets
      Select Case Ws.Name
         Case "SPVList", "Sheet2", "Sheet3"
         Case Else
            For i = 0 To UBound(Ary)
               Ws.UsedRange.Replace Ary(i, 1), Ary(i, 2), xlWhole, , False, , False, False
            Next i
      End Select
   Next Ws
End Sub

 

[matratus34]

Fantastic - much appreciated - Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[matratus34]

You're welcome & thanks for the feedback.

I just tested and got a Runtime 9 / Subscript out of Range error

I've changed:
For i = 0 To UBound(Ary)
to

For i = 1 To UBound(Ary)

and it runs - does that seem ok?

 

[Fluff]

Yes that's fine, it was a mistake on my part.

 

[matratus34]

No problem - your help has been great

 

[Fluff]

You're welcome & thanks for the feedback.