Macro for extracting data to another work sheet

im an admin and this macro makes my life easy..so i appreciate if you help me write the code

Lets say we have these columns

Sarah Smith 1234566 UK
Homer Simpson 3456677 USA
Max Power 4567932 Canada
Meg Griffin 5689321 USA
Maple Canucks 9864221 Canada
So I wanna exctract all data regarding USA in one worksheet, UK in another and Canada in another.. First how do you do it? I wanna make a macro so in future if i add more names it automatically adds to appropriate worksheet (country)

How is the data structured? Cells in different columns or e.g. 3 columns of data?

It would help if you uploaded your workbook example with your wanted output ("before" and "after")

Since it has office info I cant
Here is my sheet1


ColumnA ColumnB ColumnC
Name Student# Country
Sarah Smith 1234566 UK
Homer Simpson 3456677 USA
Max Power 4567932 Canada
Meg Griffin 5689321 USA
Maple Canucks 9864221 Canada
So I wanna extract all data regarding USA in one worksheet, UK in another and Canada in another.. First how do you do it? I wanna make a macro so in future if i add more names it automatically adds to appropriate worksheet (country)

I'll have a look

You are AMAZING! thank you

I hope that the attached workbook example will work

I CAN NOT Thank you enough..i was entering oe by one by hand..ugh
WOW THANK YOU THANK YOU THANK YOU

Glad to help

Hi..

Heres an alternative that uses autofilter in a self contained Sub..

The unique values that are used in the autofilter criteria come from a method i have seen Jindon use..

Please Login or Register  to view this content.

THANK YOU! You guys are AMAZING

Hi..

I wanna make a macro so in future if i add more names it automatically adds to appropriate worksheet (country)

I missed that part.. I added a "Transferred" column.. so now.. whenever a row gets transferred.. it gets a "Y" shown beside it..

Only rows that have NOT been transferred (have no "Y" next to them) will be transferred next time you run it.. so in essence.. you can keep your Master list in tact (adding to it at will) and have no duplicates on your Country sheets..

Heres a slight improvement..

Please Login or Register  to view this content.

well, I was wrong, country is on first column no 3rd..I changed the 3 in 1 but things got screwed up
@apo, I tried your original code works on small scale,in big scale it elimnates a column in each sheet.. second code , showed random weird stuff

Hi.. both of my codes worked for me using the sample workbook you gave..
If you are having issues applying the code to s larger/different workbook, probably best if you can attach your real which with anoninised data.._

Click on the "Go Advanced" button which is below to the right of the "Quick Reply" box..

Then Click on the "Manage Attachments" button..

canucks

Try

Please Login or Register  to view this content.

It is too risky for the OP to receive the suggestion when the poster doesn't really understand what it does....

Hi Jindon..

I presume you refer to this code..

Please Login or Register  to view this content.

?

Mate.. I am doing the best i can.. been active with VBA (as a hobby) for only about 8 months now.. so I feel i am progressing ok..

I did ask in another thread (on Ozgrid), if you could explain exactly what that code is doing.. but you must have missed it.. I know you are quite busy answering heaps of posts..

So .. for now.. it is a means to an end.. it finds me uniques in a column.. and does it well... but yes.. I would love to understand what it does.. (seriously.. who wouldn't?).. care to share with all of us?

Cheers

Originally Posted by apo

Mate.. I am doing the best i can.. been active with VBA (as a hobby) for only about 8 months now.. so I feel i am progressing ok..

8 month, 10 years doesn't matter.
You should not post a code as a "Solution" for someone else's problem when you don't know what/how it does.
You should practice for yourself and find out what it does from the web before you post.

So .. for now.. it is a means to an end.. it finds me uniques in a column.. and does it well... but yes.. I would love to understand what it does.. (seriously.. who wouldn't?).. care to share with all of us?

It creates as array of unique value converted from array formula through Evaluate method.
It has a limitations.

k.. thanks for the advice.. really appreciate it..

but I must say.. everything i have learn in regards to VBA is from forums like this one.. so when you see something that "does the job".. and does it really effectively.. its so tempting to dabble in it.. even when (as you pointed out).. the what and how is unknown...

But yes.. practice makes perfect.. I will agree..