title: Loop Through Rows To Find Value And Copy Data
body: <p>I have a Workbook that imports data from a vendor sheet. The vendor Worksheet Column A is dynamic so it will change from time to time. The import file should copy the appropriate data to an input sheet in my workbook so the data can be verified before transferring to a log. I created a For Loop to loop through all the rows in column A looking for a specific value. Once the value is found I used the OFFSET function to copy data from different columns in the same row. As you will see they are not successive columns. The code works fine for copying data from one column, however when I try to copy all the necessary data from each column it returns nothing. Could you please help me understand why this is happening? Thank you so very much for any and all help.</p>
  <pre><code>Sub ImportData()
  
  Dim FileOpen As Variant
  Dim OpenBook As Workbook
  Dim i As Integer
  
  Application.ScreenUpdating = False
  FileOpen = Application.GetOpenFilename(Title:=&quot;Browse for your File &amp; Import Range&quot;, FileFilter:=&quot;Excel Files(*.xls*),*xls*&quot;)
  
  If FileOpen &lt;&gt; False Then
  
      Set OpenBook = Application.Workbooks.Open(FileOpen)
  
      OpenBook.Sheets(1).Range(&quot;E11:F100&quot;).Replace What:=&quot;U&quot;, Replacement:=&quot;&quot;
      OpenBook.Sheets(1).Range(&quot;E11:F100&quot;).Replace What:=&quot;i&quot;, Replacement:=&quot;&quot;
  
  
      'Date &amp; Time
      OpenBook.Sheets(1).Range(&quot;E9&quot;).Copy
      ThisWorkbook.Worksheets(&quot;Input&quot;).Range(&quot;B10&quot;).PasteSpecial xlPasteValues
      OpenBook.Sheets(1).Range(&quot;E10&quot;).Copy
      ThisWorkbook.Worksheets(&quot;Input&quot;).Range(&quot;B11&quot;).PasteSpecial xlPasteValues
      OpenBook.Sheets(1).Range(&quot;F9&quot;).Copy
      ThisWorkbook.Worksheets(&quot;Input&quot;).Range(&quot;C10&quot;).PasteSpecial xlPasteValues
      OpenBook.Sheets(1).Range(&quot;F10&quot;).Copy
      ThisWorkbook.Worksheets(&quot;Input&quot;).Range(&quot;C11&quot;).PasteSpecial xlPasteValues
      'Plant Name
      OpenBook.Sheets(1).Range(&quot;B4&quot;).Copy
      ThisWorkbook.Worksheets(&quot;Input&quot;).Range(&quot;D11&quot;).PasteSpecial xlPasteValues
      'pH
      For i = 11 To 100
          If OpenBook.Sheets(1).Range(&quot;A&quot; &amp; i).Value = &quot;Sales&quot; Then
              OpenBook.Sheets(1).Range(&quot;A&quot; &amp; i).Offset(0, 5).Copy
              ThisWorkbook.Worksheets(&quot;Input&quot;).Range(&quot;B24&quot;).PasteSpecial xlPasteValues
              OpenBook.Sheets(1).Range(&quot;A&quot; &amp; i).Offset(0, 6).Copy
              ThisWorkbook.Worksheets(&quot;Input&quot;).Range(&quot;C24&quot;).PasteSpecial xlPasteValues
              OpenBook.Sheets(1).Range(&quot;A&quot; &amp; i).Offset(0, 2).Copy
              ThisWorkbook.Worksheets(&quot;Input&quot;).Range(&quot;D24&quot;).PasteSpecial xlPasteValues
          End If
      Next i
      OpenBook.Close False
  End If
  Application.ScreenUpdating = True
  
  End Sub
  </code></pre>