title: looping through range and copying to range
body: <p>I am having a really hard time copying rows from a loop to a loop. The destiniation loop is all blank cells. I have been stuck on this for 3 days now, i feel so unproductive. What am i missing? </p>
  
  <pre><code>Sub Testloop()
  Dim a As Range, b As Range, d As Range
  
  Sheets("SAP Output DATA").Select
  Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Select
  Selection.SpecialCells(xlCellTypeBlanks).Offset(0, 4).Select
  Set d = Selection
  
  Sheets("Input DATA").Select
  Range("B2").Select
  Range(Selection, Selection.End(xlToRight)).Select
  Range(Selection, Selection.End(xlDown)).Select
  Set a = Selection
  
  For Each b In a.Rows
  b.Copy
      For Each row In d.Rows
          b.PasteSpecial
      Next row
  Next b
  
  End Sub
  </code></pre>
  
  <p>It copies the data over, but the data it copies over is all original data from row 2, the next blank cell doesnt contain the next row data from the Input DATA sheet. How can i loop through the rows and paste them into the blank cells? </p>
  
  <p>see images:</p>
  
  <p>1.
      <a href="http://i.stack.imgur.com/Jd95G.png" rel="nofollow">http://i.stack.imgur.com/Jd95G.png</a></p>
  
  <p>2.
      <a href="http://i.stack.imgur.com/444RO.png" rel="nofollow">http://i.stack.imgur.com/444RO.png</a></p>
  
  <p>After a long day, i still cant solve it. This is as close as i think ill ever get it. </p>
  
  <pre><code>Sub Testshttestonemoretime()
      Dim a As Range, b As Range, d As Range, f As Range
      Dim i As Long, r As Range, coltoSearch As String
      Dim sht As Worksheet
  
      Set sht = ThisWorkbook.ActiveSheet
  
      Sheets("Input DATA").Select
      Range("B2").Select
      Range(Selection, Selection.End(xlToRight)).Select
      Range(Selection, Selection.End(xlDown)).Select
      Set a = Selection
  
  Sheets("SAP Output DATA").Select
  For Each b In a.Rows
  MsgBox b.Address
  
      For Each Address In b
  
      coltoSearch = "A"
      For i = 2 To Range(coltoSearch &amp; Rows.Count).End(xlUp).row
      Set r = Range(coltoSearch &amp; i)
  
          If Len(r.Value) = 0 Then
              MsgBox "No Value, in " &amp; r.Address
              b.Copy Destination:=Cells(i, 5)
          End If
  
      Next i
  
      Next Address
  Next b
  
  End Sub
  </code></pre>
  
  <p>Excel Sheet to download with the problem:</p>
  
  <pre><code>https://drive.google.com/file/d/0B-ZY6BZH9zh5WGpuY0RPZk5Mb2c/view?usp=sharing
  </code></pre>
  
  <p>the button is called "Copy text to color" on sap data sheet</p>
  
  <p>so Far the only thing i got working in a way that works, mostly. I have no idea why it fails sometimes; is this:</p>
  
  <pre><code>Sub WorkingLoop()
      Dim a As Range, b As Range, d As Range, f As Range, e As Range
      Dim i As Long, r As Range, coltoSearch As String
      Dim sht As Worksheet
  
      Set sht = ThisWorkbook.Worksheets("Input DATA")
  
      Sheets("Input DATA").Select
      Range("B2").Select
      Range(Selection, Selection.End(xlToRight)).Select
      Range(Selection, Selection.End(xlDown)).Select
      Set a = Selection
  
  Sheets("SAP Output DATA").Select
  
  For Each b In a.Rows
  'MsgBox b.Address
  
  Set f = sht.Range(b.Address)
  f.Copy
      coltoSearch = "A"
      For i = 2 To Range(coltoSearch &amp; Rows.Count).End(xlUp).row
      Set r = Range(coltoSearch &amp; i)
  
  
          If Len(r.Value) = 0 Then
              'MsgBox "No Value, in " &amp; r.Address
              Set e = Range(r.Address)
  
              For Each cell In e
              e.PasteSpecial
              Next cell
          End If
  
      Next i
  e.PasteSpecial
  Next b
  
  
  End Sub
  </code></pre>