title: VBA Excel copy data from one workbook to another when specified string appears in the row
body: <p>I have the situation presented below in the image (Workbook 1):</p>
  <p><a href="https://i.stack.imgur.com/mkjSj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mkjSj.png" alt="enter image description here" /></a></p>
  <p>and below (Workbook 2)</p>
  <p>I want to copy my record from workbook 1 to workbook 2  if</p>
  <ul>
  <li>in the Workbook 1 column A the string &quot;surveyor&quot; appears</li>
  <li>the value from column B, which is exactly in the same row, where the string &quot;suveyor&quot; was found.</li>
  </ul>
  <p>Then I would like to copy this value to my workbook 2.</p>
  <p>I have prepared the code like this:</p>
  <pre><code> Sub FrontsheetAdd3()
   Dim x As Worksheet, y As Worksheet, sPath As String
   Dim i As Long
  
   sPath = ThisWorkbook.Path &amp; &quot;\Survey_form.csv&quot;
   Set x = Workbooks.Open(sPath)
  
   Set y = ActiveWorkbook.Sheets(&quot;Frontsheet&quot;) 'set to current worksheet name
  
  'Name of the sheet is the same as Name of the workbook 1
   If x.Sheets(&quot;Survey_form&quot;).Range(&quot;A&quot; &amp; i).Value = &quot;surveyor&quot; Then
   x.Sheets(&quot;Survey_form&quot;).Rage(&quot;B&quot; &amp; i).Value = (&quot;A&quot; &amp; i)
   y.Sheets(&quot;Frontsheet&quot;).Range(&quot;D34&quot;).PasteSpecial
   End If
   Next i
  
   End Sub
  </code></pre>
  <p>I have an error:</p>
  <p><strong>Method or data member not found</strong></p>
  <p>at the line</p>
  <pre><code>   If x.Sheets(&quot;Survey_form&quot;).Range(&quot;A&quot; &amp; i).Value = &quot;surveyor&quot; Then
  </code></pre>
  <p>UPDATE:</p>
  <p>After changing my code, which now looks like this:</p>
  <pre><code> Sub FrontsheetAdd3()
   Dim x As Workbook, y As Workbook, sPath As String
   Dim i As Long
  
   sPath = ThisWorkbook.Path &amp; &quot;\Survey_form.csv&quot;
   Set x = Workbooks.Open(sPath)
  
   Set y = ActiveWorkbook.Sheets(&quot;Frontsheet&quot;) 'set to current worksheet name
  
   'Name of the sheet is the same as Name of the workbook 1
    For i = 1 To 40
    If x.Sheets(&quot;Survey_form&quot;).Range(&quot;A&quot; &amp; i).Value = &quot;surveyor&quot; 
     Then
    x.Sheets(&quot;Survey_form&quot;).Rage(&quot;B&quot; &amp; i).Value = (&quot;A&quot; &amp; i)
    y.Sheets(&quot;Frontsheet&quot;).Range(&quot;D34&quot;).PasteSpecial
    End If
    Next i
  
    End Sub
  </code></pre>
  <p>At the line:</p>
  <pre><code>      Set y = ActiveWorkbook.Sheets(&quot;Frontsheet&quot;) 'set to current worksheet name
  </code></pre>
  <p>my active workbook (Workbook2), where the macro is meant to be is closing down and error <strong>Subscript out of range</strong> emerges.</p>
  <p>What is missig then?</p>