How to extract specific data only and create multiple sheets

Dear All,
Thanks for reading. I'm attaching an example file. Basically, what I need is to get data for specific variables for specific countries only. In sheet 1, there are different countries in column 1, each country is repeated for 9 rows for 9 different variables in column 2 and remaining columns are years. Its just a very small sample of 5 countries and 9 variables. In actual sheet I have 180 countries and 30-40 variables. So, lets say I just need to extract data for 2 countries out of 5 and for just 3 variables out of 9. And, I want data for each country available in separate sheets which name themselves by the country name. To demonstrate, i extracted data for china and russia in my example file and created sheets china and russia with data for only 3 variables. Ofcourse I did it manually, but i was wondering if i can write a program telling names of the countries and variables and excel extracts that data in different sheets?
Perhaps it could be done with functions alone.
Thanks very much.

Perhaps this macro is could be of use. Macro will create sheets if they don't exists and sort the "country" sheets alphabetically.

Please Login or Register  to view this content.

Alf

Thanks so much! I tried the macro on my test file and it worked, although I guess i have to run the macro every time i want a country sheet created. I was wondering if there is a way to write all country names we need in one single macro and it creates all the sheets in one go? Anyways its not very important as I can always run the macro for each country. But my more important question is that how do I modify this macro for my original files. I mean here i just needed two variables to extract which i had specified. What if in a much larger file with 100's of different countries, there are 35 variables and I need some of those only. In the macro you provided where will that information go? Because I dont see names of the variables anywhere in the macro code you gave.
Thanks very much!

I was wondering if there is a way to write all country names we need in one single macro and it creates all the sheets in one go?

That should not be impossible using perhaps an input box to create an array for selected countries and specified variables and read this to the autofilter setting one country at a time but all variables in one go. Macro probably also need to check if "country" sheet exists. If so delete sheet first and then create a new sheet with the "new" selected variables.

Not sure I'm up to it but as it is an interesting challenge I'll have a go at it over the week end.

You need to adjust your data a bit at the moment there are two variables both named "Exports" so you need to change name a bit on one of them.

Alf

Thanks again! Sorry, i missed that export bit. I have modified the data in the test file attached to this reply. There are two extra columns and what i need is to create specific variable sheets for specific countries only. So, a GDP sheet will have data for only those countries specified in the macro. For example, I will need sheets for GDP, EXPORTS AND OIL for 3 countries out of 5 (in original sheet i have 200 countries and 500 variables and i need data for 25 countries for 10 variables defined by variable codes.
What will be the macro for that?

Awesome stuff, sir! Thanks so much and I'm sorry for changing the request without being very clear. So, basically, I want everything as you had solved. The only thing is that rather than having sheets for countries, I need sheets for variables such as GDP, EXPORTS, OIL etc. I tried to edit your macro, but was not successful entirely. Please if you can modify the macro or let me know what to change in your macro. Thanks a million!

Ok, I modified the exciting file and the data input is the same as before i.e. in the "List" sheet you fill in the relevant countries and criteria.

You now got two macros one named "CountryFilter" this is the same as the earlier macro and will give you tabs for different countries with the selected criteria.

The other macro is called "CriteriaFilter" and this will create a tab for every criterion you added to the list sheet with the selected countries found in the "List" sheet.

If you find you have no need for the "CountryFilter" macro just delete it.

Alf

Thanks so much, sir! I got unwell, so sorry for coming back late. I tried this one and yes it works on the small file I had attached. But the original file is really huge and I dont know what happened but it crashed couple of times when I tried to use the macro. I finished the project for the time being, but I'm sure this could be really handy. Thanks again!