Finding multiple values and copying the whole row in a new sheet

[ZuluKing22]

Hello, I have a excel table with user info. There are 1000 rows and a few columns (name, address, email...). What I would like to do is make a macro that would do the following: Search the whole table with multiple values (user street name) I input and when there is a match copy the whole row into another sheet.

So far I've been using this:

Please Login or Register  to view this content.

Sheet 3 is where i input the names of the streets I need inside column A starting from A2, sheet 2 is where the rows should be copied and column D inside sheet 1 is where all the street names are. Currently the code just copies the first row of sheet 1 no matter what is written there and ignores everything else. I'm using MS Office 2007. Every help would be appreciated. Thanks!

[Vikas_Gautam]

I guess, you need advanced filter technique.

So
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ZuluKing22]

Here are the examples. In the "BEFORE example" there is a simple list of imaginary user info on sheet1. Sheet3 is where I type in the street names I'm interested in inside column A. The macro should find all users who have the same street name as the ones I typed in inside sheet3 and copy their whole info to sheet2. You can see the result in the AFTER example.

What I'm basically trying to do is categorize users by city districts using street names that are a part of those city districts.

[MrShorty]

I will agree with Vikas_Gautam -- this sounds like a job for advanced filter (I'm sure one can write a macro for the job, but why reinvent the wheel?)

https://support.office.com/en-US/art...rs=en-US&ad=US
If you prefer a video tutorial https://support.office.com/en-US/art...7-6AD3774DC420

[Vikas_Gautam]

Here is the required solution.

You can add more conditions. Just make sure column heading in the Criteria Range(cRng) must match with that of Master Table(mRng).
You can also use Wild Cards in Criterias.

Here is the link to learn Advanced filter basics.
http://www.contextures.com/xladvfilter01.html

Check the attached file:-

[ZuluKing22]

Vikas_Gautam and MrShorty, excelforum's MVPs! Thank you!