Find & Replace All Sheets Using VBA

Thanks in advance!

I need to find column a and replace with column B text across all sheets. The code I have is only doing the find and replace on one sheet? Any tips?

Sub SearchReplaceMacro()

    Cells.Replace What:=Range("A1").Text, Replacement:=Range("B1").Text, LookAt:=xlWhole, _
        SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A2").Text, Replacement:=Range("B2").Text, LookAt:=xlWhole, _
        SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A3").Text, Replacement:=Range("B3").Text, LookAt:=xlWhole, _
        SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A4").Text, Replacement:=Range("B4").Text, LookAt:=xlWhole, _
        SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A5").Text, Replacement:=Range("B5").Text, LookAt:=xlWhole, _
        SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A6").Text, Replacement:=Range("B6").Text, LookAt:=xlWhole, _
        SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A7").Text, Replacement:=Range("B7").Text, LookAt:=xlWhole, _
        SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

Correction on Code..

Option Explicit

Sub SearchReplaceMacro()

    Cells.Replace What:=Range("A1").Text, Replacement:=Range("B1").Text, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A2").Text, Replacement:=Range("B2").Text, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A3").Text, Replacement:=Range("B3").Text, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A4").Text, Replacement:=Range("B4").Text, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A5").Text, Replacement:=Range("B5").Text, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A6").Text, Replacement:=Range("B6").Text, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Range("A7").Text, Replacement:=Range("B7").Text, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

Option Explicit
Sub AhhSheetsSearchReplace()
 Dim Sh As Worksheet
 For Each Sh In ThisWorkbook.Worksheets
    With Sh
        .Cells.Replace What:=.Range("A1").Text, Replacement:=.Range("B1").Text, LookAt:=xlWhole, _
            SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        .Cells.Replace What:=.Range("A2").Text, Replacement:=.Range("B2").Text, LookAt:=xlWhole, _
            SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        .Cells.Replace What:=.[A3].Text, Replacement:=Sh.[B3].Text, LookAt:=xlWhole, _
            SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        .Cells.Replace What:=.[A4].Text, Replacement:=.[B4].Text, LookAt:=xlWhole, _
            SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        .Cells.Replace What:=.[A5].Text, Replacement:=.[B5].Text, LookAt:=xlWhole, _
            SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        .Cells.Replace What:=.[A6].Text, Replacement:=.[B6].Text, LookAt:=xlWhole, _
            SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        .Cells.Replace What:=.[A7].Text, Replacement:=.[B7].Text, LookAt:=xlWhole, _
            SearchOrder:=xlPart, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
 Next Sh
End Sub

Thanks! That worked faster. However it is only finding and replacing in row A and row B across all sheets... I want it search entire worksheet for text shown in "row A" and replace any instance of it with the corresponding text in "row B" . I do not want it to match entire cell content.

Is that possible?

getting error.. Capture.PNG

Originally Posted by HSV

I have a file imitated, but no error to me.
Maybe you could upload your file so we can see where things go wrong.

Here ya go! And thanks again.

Originally Posted by HSV

Your length of your words may have a length to 255 characters.
Sheet 1 cell B6 has 461 characters.

Shortened it and it still is only replacing rows A & B only, its not finding instances of A in entire sheet and replacing all instances of A with B... lol. I've been trying to get this to work for 2 days.

Originally Posted by HSV

Replace by 'xlwhole' 'xlpart'

THANK YOU!!!!!!!!!!!!

I could give you hug!

-Tierra