Copy all the rows from one sheet to another which depends on one parameter

[S1n1]

For example I have a list of data on sheet1:
Gratz1
Gratz5
Gratz9

I have a list of the data on Sheet2:
Grazt0 87 Red
Gratz1 1900 Blue
Gratz1 1760 Green
Gratz3 5569 White
Gratz5 592 Brown
Gratz5 14 Red
Grazt5 3698 Black
Gratz10 14 White
Gratz9 1 Black
Gratz9 369 White

Is it possible to make that macro finds all the necessary rows in the sheet2 by the given parameter in the sheet1 and copy all of them on the sheet3?

Thanks in advance guys!

[BlazzedTroll]

I'm new to VBA and I'm not sure I complete understand what you are going for but you can try something like this (someone else may want to verify my logic/code)

Please Login or Register  to view this content.

if you don't want to use a row counter like I did you can try

Please Login or Register  to view this content.

after the .copy but I'm not as fond of that and I don't completely understand it. So I just do my own thing.

hope this helps get the thought train rolling.

[AB33]

S1n1,
It could be done, but need a sample in excel book. To attach a sample, go to advance then attachment.

[tigeravatar]

S1n1,

Assumptions:
Your data has headers in row 1 so actual data starts in row 2 for all three sheets
The list of data on sheet1 is in column A
The table of data on sheet2 is in columns A, B, and C

If those are the case, then give this a try:

Please Login or Register  to view this content.

[S1n1]

Thanks guys very much, but still it's not working.
I attached an example.
Sheet1 - my data which i want to find. It matters only column A cause I want to look just for that column's data in sheet2, in this example I want to look for Home and Yacht
Sheet2 - all the data, where I want to find my rows. In this example I want to search for Home and for Yacht, so on the sheet3 I would like to get pasted data from sheet2 (all the lines with "Home" (5,6,7) and all the lines with "Yacht" (2).
The difference is that in production I have loads of data and load of column to be pasted. Hopefully you've got my idea. Thanks very much.

[tigeravatar]

S1n1,

Because the "Place" column on Sheet2 is in column B, change the 1 to a 2 in the .Autofilter line, like so:

Please Login or Register  to view this content.

Now it is searching the second column (which is column B) for the values listed on Sheet1 column A (Yacht and Home).

[S1n1]

Ok, now I've got the logic of the macro, thanks so much!! Works amazing

[AB33]

Try the attached.

[S1n1]

Works perfect as well, thanks!