NEWBIE!! HELP w/ pasteing data

[BIGTONE559]

Lost as to the proper syntax when trying to paste rows of cells to "J5" and keep a running list

Sub Help_ME()



Dim cell As Range
Dim dataRange As Range

Set dataRange = Range("G5:G30")


For Each cell In dataRange

If cell.Value = True Then

Range(Cells(cell.Row, 1), Cells(cell.Row, 5)).Copy


msgBox("Blah blah blah")
   
    
Else


End If


Next cell

End Sub

The message box would show me that it was selecting the proper values (and will be removed) however i don't know how to copy those values into Cells Beginning with "J5" i originally used range("J5") but it would overwrite the existing values as it looped.

please help!

 

[VoG]

Maybe like this

If cell.Value = True Then
    LR = WorksheetFunction.Max(5, Range("J" & Rows.Count).End(xlUp).Row + 1)
    Range(Cells(cell.Row, 1), Cells(cell.Row, 5)).Copy Destination:=Range("J" & LR)

 

[Ralajer]

You were almost there you just need a variable to track the output row and a way to define the output range. This incorporates both.

Sub Help_ME()

    Dim cell As Range
    Dim dataRange As Range
    Dim DestRow As Long
    
    Set dataRange = Range("G5:G30")
    
    '// Set the first row for the output
    DestRow = 5
    
    For Each cell In dataRange
        If cell.Value = True Then
            Range(Cells(cell.Row, 1), Cells(cell.Row, 5)).Copy _
                Destination:=Cells(DestRow, 10)
            '// Increment the Destination Row
            DestRow = DestRow + 1
        End If
    Next cell
End Sub

This is another method of doing the same thing I think it might be a little faster as the copy and paste operation is only done once, after it has identified the ranges to copy.

Sub Help_ME2()

    Dim cell As Range
    Dim dataRange As Range
    Dim pasteRng As Range
    Dim cpyRng As Range
    
    Set dataRange = Range("G5:G30")
    Set pasteRng = Range("J5")
    
    For Each cell In dataRange
        If cell.Value = True Then
            '// For first range to be copied
            If cpyRng Is Nothing Then
                '// Set the start of the copy range to first match
                Set cpyRng = Range(Cells(cell.Row, 1), Cells(cell.Row, 5))
            Else
                '// Join additional match ranges to the existing range
                Set cpyRng = Union(cpyRng, _
                                Range(Cells(cell.Row, 1), Cells(cell.Row, 5)))
            End If
        End If
    Next cell
    '// Copy and paste ranges that match criteria
    cpyRng.Copy Destination:=pasteRng
End Sub

 

[BIGTONE559]

I'd like to thank both of you for helping me out. I had problems understanding the syntax after using the .Copy

Appreciate all the help