Search code examples
vbaexcelforeachcopy-paste

Copying dynamic range in for-each loop


I've spent the past couple weeks trying to piece together a solid piece of VBA from other suggestions in order to complete this task, but now I'm turning to you all for an answer. I'm trying to copy a range from ws2, columns A:K of the row which starts with the value c that matches the value i in ws1 into the row which starts with the value i in ws1. The criteria being copied are 1s and 0s. It's basically a glorified looped v-lookup that pastes a dynamic range.

I figured out the looping mechanism, now I just need help composing the code for copying the selected cells over.

Here's what I have so far:

For Each i In ws1.Range("A4:A26")
    For Each c In ws2.Range("A8:A28")
        If i.Cells.Value <> c.Cells.Value Then
            'select columns A:K in the row with the value c in ws2
            'open ws1
            'paste selection to[starting at] column D in ws1
            Exit For

        End If
    Next c
Next i

Solution

  • If you really want the matches, and once a match is found, then Exit the For.
    This does it with out activating or selecting

         For Each i In ws1.Range("A4:A26")
             For Each c In ws2.Range("A8:A28")
                 If i.Value = c.Value Then
                     'select the row, columns A:K that starts with the value c in ws2
                     ws2.Range("A" & c.Row & ":K" & c.Row).Copy ws1.Range("A" & i.Row)
                     Exit For
                 End If
             Next c 
         Next i