Find and replace in Excel

Question

I have a table in sheet 1 has two columns A and B. I created a macro to find the value of A1 in sheet 2 and replace the value of B1 instead of value A1 in sheet 2

I am looking to improve the macro to cover the whole range of A and B columns.

    Sub Find_Replace()
'

'
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.Replace What:="Value 1 ", Replacement:="value X", LookAt:= _
        xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

sample table

** looking to improve Range("A1").Select to cover all available cells in range A and B

Answer 1

Try this code. Sample range is A1:A10 in Sheet1. B1:B10 is automatically referred using Offset method in the VBA code. However the limitation as I mentioned in the above comment applies. Sheets are Sheet1 and Sheet2 in the same workbook.

Do try this code and revert back.

Sub Macro1()
Set myrange = Sheet1.Range("A1:A10")
For Each cell In myrange

Dim find1
Dim replace1

find1 = cell.Value
replace1 = cell.Offset(RowOffSet:=0, ColumnOffset:=1).Value

    Sheet2.Cells.Replace What:=find1, Replacement:=replace1, LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Next cell
End Sub