VBA Help - paste in next row in range while in For

Question

Having trouble pasting data found in For loop into the next available row of different range on the same sheet. The range I'm trying to paste to is ("A295:A324"). I'm only using one sheet ("Printout") and never any others. He're's what I have so far

Sub CmpnyWkLoad_autofil()
        lr = 338    'last row
        lc = 69     'last column
        For r = 332 To lr  'start at row 332
        current = False
            For c = 60 To lc     'start at column BH
                If Cells(r, c).Value >= Range("V4").Value Then current = True: Exit For
             Next c
            If current Then Columns("A:AG").Rows(r).Copy
         Next r
End Sub

What I'm looping/reading is pre-filled dates in columns ("BH:BQ") and if they are after today's date, it copies the first 33 cols in same row and pastes it to the next available row in ("A295:A324") Then continues loop to end. Any help finishing this would be much appreciated.

I see you copy the row (though it's the entire row and not just A:AG, but I never see you paste it — tigeravatar
All ten columns (BH:BQ) have dates? And you need to compare them all to todays date? Your question is quite unclear. Can you illustrate your data and expected result? — L42

L42 L42 · Accepted Answer · 2014-06-13T02:30:27

If my assumptions in the comment is correct you can try this:

Dim lr As Long, cel As Range

With Thisworkbook.Sheets("PrintOut")
    '~~> Check up to where your last row in BH is
    lr = .Range("BH" & .Rows.Count).End(xlUp).Row '~~> you can also use lr = 338
    '~~> You said BH row starts at 332
    For i = 332 To lrow
        For Each cel In .Range("BH" & i, "BQ" & i)
            If cel.Value >= .Range("V4").Value Then
                .Range("A" & cel.Row, "AG" & cel.Row).Copy
                .Range("A" & .Rows.Count).End(xlUp) _
                .Offset(1, 0).PasteSpecial xlPasteValues, , , True
                Exit For
            End If
        Next
    Next
End With

HTH

VBA Help - paste in next row in range while in For

1 Answers