[Solved] Change VBA code to find and replace in multiple worksheets [duplicate]

by


Use the sheets property of the workbook to return a collection that you can loop through with For Each. Skip over the Names sheet and avoid reading the names sheet multiple times by putting the values into an array.

Option Explicit
Sub multiFindandReplace()
    Dim wb As Workbook, ws As Worksheet
    Dim myList As Variant, myRange As Range
    Dim i As Long, msg As String
     
    ' find/replace list
    Set wb = ThisWorkbook ' or ActiveWorkbook
    myList = wb.Sheets("Names").Range("A1:B238").Value2
    
    For Each ws In wb.Sheets
        If ws.Name <> "Names" Then
            Set myRange = ws.Range("A1:Y99")
            For i = LBound(myList) To UBound(myList)
                If Len(myList(i, 1)) > 0 Then
                    myRange.Replace _
                      What:=myList(i, 1), _
                      Replacement:=myList(i, 2), _
                      LookAt:=xlWhole
                End If
            Next i
            msg = msg & vbCr & ws.Name
        End If
    Next
    MsgBox "Sheets processed :" & msg, vbInformation, wb.Name
End Sub

solved Change VBA code to find and replace in multiple worksheets [duplicate]