title: Match and copy data between worksheets
body: <p>I have two worksheets, one main and other that is constantly changing. I want to compare data and if there is a match, copy data to the main worksheet.</p>
  
  <p>I have created a userform inside the main worksheet where there is a textbox asking the file name ("Change" in this example).</p>
  
  <p>After I have inserted the file name “Change“ and press ok, it should open that worksheet and another userform with 3 text boxes (<code>Ref;City;Data</code>) and an OK button.</p>
  
  <p>So I can manually fill the userform with the correct columns in the textboxes by looking at the Excel file:
  <code>Ref=E;City=G; Data=I.</code></p>
  
  <p>Pressing OK, should fill the “Main“ worksheet by matching information on two columns and copying the info in the columns that I have specified to the "Main" Worksheet, and close the "Change" worksheet.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I believe I have improved the code a bit.</p>
  
  <p>To open the worksheet by the text box, followed by a new UserForm:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
      myfile = "C:\Users\ss\Desktop\Reports\" &amp; TextBox1.Value
      Application.Workbooks.Open Filename:=myfile
      Windows.Arrange ArrangeStyle:=xlVertical
      UserForm1.Show
  
  End Sub
  </code></pre>
  
  <p>To match Values between workbooks and copy different columns specified:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
      Dim Values As Range
      Dim V As Range
      Dim Ref As Range
      Dim City As Range
      Dim Data As Range
      Dim x1, y1, y2, y3, V1, V2, V3, V4
  
      Application.ScreenUpdating = False
      Workbooks("Main.xlsm").Activate
      Sheets("Sheet1").Select
  
      Set Values = Range(Range("B4"), Range("B4").End(xlDown))
  
      For Each V In Values
          x = V.Value
  
          Workbooks("Change.xlsx").Activate
          Sheets("Sheet1").Select
          V1 = TextBox1.Value
          V2 = TextBox2.Value
          V3 = TextBox3.Value
          V4 = TextBox4.Value
  
          With Worksheets("sheet1").Columns("C:I")
              On Error Resume Next
              Set Cfind = .Cells.Find(what:=x, lookat:=xlWhole)
  
              If Cfind Is Nothing Then GoTo line1
  
              y1 = Cfind.Columns("V2").Value
              y2 = Cfind.Columns("V3").Value
              y3 = Cfind.Columns("V4").Value
  
              Application.ScreenUpdating = False
              Workbooks("Main.xlsm").Activate
              Sheets("Sheet1").Select
          End With
  
          V.Offset(0, 1) = y1
          V.Offset(0, 2) = y2
          V.Offset(0, 3) = y3
  
  line1:
      Next V
  
  End Sub
  </code></pre>
  
  <p>I can't see anything wrong and when I run this, there is no error.</p>
  
  <p><strong>Edit</strong>
  I've changed the code to define the ranges that I want and it seems that its working but the matching and copy isn't.</p>
  
  <pre><code>Private Sub CommandButton1_Click() 
  
      myfile = "C:\Users\sst1brg\Desktop\Relatorios\" &amp; TextBox1.Value 
      Application.Workbooks.Open Filename:=myfile 
      Windows.Arrange ArrangeStyle:=xlVertical 
  
      Dim Values As Range 
      Dim Ref As Range 
      Dim City As Range 
      Dim Data As Range 
      Dim ValuesRng As Range 
      Dim RefRng As Range 
      Dim CityRng As Range 
      Dim DataRng As Range 
      Dim Cfind As Range 
      Dim OriValues As Range 
      Dim V As Range 
      Dim x As String 
      Dim x1, y1, y2, y3 
  
      Workbooks("Change.xlsx").Activate 
      Sheets("Sheet1").Select 
      Windows("Change.xlsx").Activate 
  
      Set Values = Application.InputBox( _ 
      prompt:="Select the Values cell", Type:=8) 
      Set ValuesRng = ActiveWorkbook.Sheets("Sheet1").Range(Values, Values.End(xlDown)) 
  
      Set Ref = Application.InputBox( _ 
      prompt:="Select the Ref cell", Type:=8) 
      Set RefRng = ActiveWorkbook.Sheets("Sheet1").Range(Ref, Ref.End(xlDown)) 
  
      Set City = Application.InputBox( _ 
      prompt:="Select the City cell", Type:=8) 
      Set CityRng = ActiveWorkbook.Sheets("Sheet1").Range(City, City.End(xlDown)) 
  
      Set Data = Application.InputBox( _ 
      prompt:="Select the Data cell", Type:=8) 
      Set DataRng = ActiveWorkbook.Sheets("Sheet1").Range(Data, Data.End(xlDown)) 
  
      Application.ScreenUpdating = False 
      Workbooks("Main.xlsm").Activate 
      Sheets("Sheet1").Select 
  
      With Worksheets("Sheet1") 
          Set OriValues = Range(Range("B3"), Range("B3").End(xlDown)) 
          For Each V In OriValues 
              x = V.Value 
  
              Application.ScreenUpdating = False 
              Workbooks("Change.xlsx").Sheets("Sheet1").Activate 
  
              With Worksheets("Sheet1").Columns("C:I") 
                  On Error Resume Next 
  
                  Set Cfind = .Cells.Find(what:=x, Lookat:=xlWhole) 
                  If Cfind Is Nothing Then Goto line1 
                  y1 = Cfind.Range("RefRng").Value 
                  y2 = Cfind.Range("CityRng").Value 
                  y3 = Cfind.Range("DataRng").Value 
              End With 
  
              Application.ScreenUpdating = False 
              Workbooks("Main.xlsm").Activate 
              Sheets("Sheet1").Select 
  
              Range("C4").Value = y1 
              Range("D4").Value = y2 
              Range("E4").Value = y3 
  line1: 
          Next V 
      End With 
  
  End Sub
  </code></pre>