Find and Replace data from different worksheet from different workbook in Excel

Question

I want to find and Replace data from two different workbook from two different worksheets.

I have tried different codes in VBA but still not get proper output. I have two columns in one worksheet in workbook A and have second worksheet into different workbook B I am trying to replace data into workbook B (column 1_Number) from workbook A replaced value(Column 1_Name) and the lookup value (column 2_Number).

Private Sub CommandButton1_Click()
        Workbook.Sheets("Sheet2").Select
        Set myrange = Sheet2.Range("A2:A878") //Set Range column wise
        For Each cell In myrange
    //Create Varibles
        Dim find1 
        Dim replace1
        find1 = cell.Value
        replace1 = cell.Offset(RowOffSet:=0, ColumnOffset:=1).Value
        Results.Cells.Replace What:=find1, Replacement:=replace1, LookAt:=xlPart, SearchOrder _
       :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next cell
    End Sub

    OR 

    Sub Macro1()
      For i = 2 To 878 //Used For Loop
      Worksheets("Sheet2").Range("A2:A878").Select //Get data from different worksheet in different workbook
      'Selection.Replace What:=Cells(i, 2).Value, Replacement:=Cells(i, 1).Value, _
      'LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
       Selection.Replace What:=Cells(i, 2).Value, Replacement:=Cells(i, 1).Value, _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
       SearchFormat:=False, ReplaceFormat:=False
       Next
       Worksheets("Sheet2").Cells(1, 1).Select
    End Sub

    OR 

    Sub Button1_Click()
    Dim sht As Worksheet
    Dim fnd As Variant
    Dim rplc As Variant
    For i = 2 To 878
    Worksheets("Sheet2").Range("B2:B878").Select //Get data in range from Column B
    'Perform the Find/Replace All
     'Selection.Replace What:=Cells(i,2).Value, Replacement:=Cells(i,3).Value,_
      sht.Cells.Replace What:=Cells(i, 2).Value, Replacement:=Cells(i, 1).Value, _  //Looking for data and replace 
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
      SearchFormat:=False, ReplaceFormat:=False
      Next //Next
      Worksheets("Sheet2").Cells(1, 1).Select //Active worksheet
    End Sub

[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — ComputerVersteher, Sep 12 '19 at 00:51
Please read the answers in the link. I know, that is exhausting, but you will benefit from understandig. You mean other code for activating a worksheet (or any oher control, like a Cell, what activates the worksheet containing the cell),? `.Activate`.Or refer to active worksheet, then simply `ActiveWorksheet`. This explains some history of the [The Macro Recorder Curse](https://rubberduckvba.wordpress.com/2019/06/30/the-macro-recorder-curse/) — ComputerVersteher, Sep 12 '19 at 01:30
Is there any script available to find data and replace it with different workbook? — Parth Shah, Sep 12 '19 at 02:42

Find and Replace data from different worksheet from different workbook in Excel

0 Answers0