# loops again

#### Davers

##### Well-known Member
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:

Code:
``````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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### north19701

##### Active Member
Code:
``````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

##### Well-known Member
Cool...thank you very much!!!

Dave

#### Joe4

Try:

Code:
``````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

##### Well-known Member
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

##### Well-known Member
No problem...again...thank you very much!!

Everyone have a good day!!

Dave

#### north19701

##### Active Member
Code:
``````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

##### Well-known Member
Very cool! Faster I'm sure!!

Thanks for taking the time out to help!!!

Dave M

Replies
10
Views
661
Replies
11
Views
823
Replies
4
Views
323
Replies
0
Views
305
Replies
5
Views
269

1,195,582
Messages
6,010,577
Members
441,557
Latest member
Jbest23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back