loops again

[Davers]

Hi everyone, this does everything I want, except starting the paste in the next available row on sheet2. The code is looking at a value in cell H1, if the cell in column A matches, it copies the row and pastes to Sheet2..but it pastes the same row as the originating data. So if the first cell that matches criteria in column A is cell A10, it copies that whole row, and pastes it into cell A10 on sheet2...I need it to paste into the next available cell on Sheet2...any ideas?

Here is my code so far:

Sub lrnLoop()

x = Range("A65536").End(xlUp).Row

For Each i In Range("A1:A" & x)
    If i.Value = Range("H1").Value Then
        i.EntireRow.Copy Worksheets("Sheet2").Range("A" & i.Row)
    End If
Next i

End Sub

Thanks for the help...

Dave

 

[north19701]

Sub lrnLoop()
a = 1
x = Range("A65536").End(xlUp).Row
For Each i In Range("A1:A" & x)
    If i.Value = Range("H1").Value Then
        i.EntireRow.Copy Worksheets("Sheet2").Range("A" & a)
        a = a + 1
    End If
Next i
End Sub

 

[Davers]

Cool...thank you very much!!!

Dave

 

[Joe4]

Try:

Sub lrnLoop()

Dim x As Long
Dim i As Range

x = Range("A65536").End(xlUp).Row

For Each i In Range("A1:A" & x)
    If i.Value = Range("H1").Value Then
        i.EntireRow.Copy Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
    End If
Next i

End Sub

Edit: Looks like I am too slow!

 

[Davers]

And thank you...I'm guessing this will do the next available?

Thanks a bunch!!!

Dave

 

[Joe4]

Yes, the only thing is was unsure if it is copying it to a page where there already is information. If there is not, the first row will be blank, but that can easily be remedied with an IF statement at the end of the macro checking the first row for data.

 

[Davers]

No problem...again...thank you very much!!

Everyone have a good day!!

Dave

 

[north19701]

Sub Macro4()
With Range("a1", Range("a65536").End(xlUp))
    .AutoFilter field:=1, Criteria1:=.Range("H1").Value
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy
End With
Sheets("Sheet2").Range("a1").PasteSpecial
Sheets("Sheet1").ShowAllData
End Sub

Here's another way that avoids looping.

 

[Davers]

Very cool! Faster I'm sure!!

Thanks for taking the time out to help!!!

Dave M