Change VBA code to find and replace in multiple worksheets

Question

I have the following code that searches words out of Sheet "names" column A in Sheet "Output" and replaces those words with "names" column B.

I am trying to extend the macro that it does the find and replace in all spreadsheets not just in "Output".

Any help is appreciated

Sub multiFindandReplace()

Dim myList, myRange
Set myList = Sheets("Names").Range("A1:B238")
Set myRange = Sheets("Output").Range("A1:Y99")
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
Next cel

End Sub

Therefore you need a second loop to loop through all worksheets. What have you tried so far? Please show your attempt and explain where it went wrong ([No attempt was made](http://idownvotedbecau.se/noattempt/)). Finally ask a question (you didn't ask one yet, see [ask]). • If you have no idea where to start, always start with researching. Your question has already been asked: [Modify VBA code for find and replace to loop through multiple Worksheets](https://stackoverflow.com/questions/45194122/modify-vba-code-for-find-and-replace-to-loop-through-multiple-worksheets). — Pᴇʜ, Jul 23 '21 at 13:35
I know that a similar question was already asked. If I would have more coding experience I would do it myself and would not ask. Anyway thanks for the tipp with the loop I will try to continue from there — wtmn, Jul 23 '21 at 13:49
Please not that this is no free coding service. People are here to assist you if you have a question/problem but you are the one who has to do it. So it is very unlikely someone does it for you and you can copy paste it if you didn't even make an attempt to solve it. If you made an attempt we can at least tell where you got wrong and how to fix it. — Pᴇʜ, Jul 23 '21 at 13:52

score 0 · Accepted Answer · answered Jul 23 '21 at 20:25

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

Change VBA code to find and replace in multiple worksheets

1 Answers1