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)
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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
55,882
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
55,882
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,882
Members
413,947
Latest member
gizmolucy

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