loops again

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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 (y)
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,968
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 17, 2002
Messages
1,165

ADVERTISEMENT

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

Thanks a bunch!!!

Dave
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,968
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 17, 2002
Messages
1,165

ADVERTISEMENT

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

Everyone have a good day!!

Dave
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
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
Joined
Sep 17, 2002
Messages
1,165
Very cool! Faster I'm sure!!

Thanks for taking the time out to help!!!

Dave M
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,024
Messages
5,856,881
Members
431,837
Latest member
megantang

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top