Copy countries from sheet 2 to sheet 3 after certain creteria macro

[abjac]

Hi. I have below test sheet, which have many countries with statistic. The layout is very nice thanks to Alphafrog for some great codes to do that.
What i need now is the possibility to choose countries to copy the data from sheet 2 to sheet 3.

The real sheet have many many countries, so it will be a great help.

I need a macro where a input box ask which countries i need to copy (i guess seperated by comma) press ok
The next input box shall ask what year. press ok

The third shall ask which months, would be nice here if i dont fill this one out it just take the upper creteria. All year i have choosen.

Then it has to copy the country data choosen to sheet 3.(can easily been seen logically in the test sheet)

If i do again it have to continue with 2 rows blank between the next run of the code.
This will give me the option to collect date for countries which are connected to each other.

So i can example collect 4 nordic countries with the year, month etc

After i can run again with 2 rows seperation example for UK, Ireland.

All and i think it logically when you check the test sheet how it has to be.

I have made how it can look in sheet 3. I selected first Argentina and Austria, year 2014 and jan,feb
After i selected Belgium, Bulgaria ,year 2014 and jan,feb

And there is 2 blank rows between the 2 runs

Please have a look, it some which will be a great for me to solve.

Thanks in advance

Sincerely

Abjac

[abjac]

Sorry maybe i asked for too much. But we could start with a code where i could select country and it will copy that country and the belonging data to sheet 3. or maybe with more countries. So if some can help with that would be nice. Is a start and i can work from there then.

Thanks in advance

Abjac

[abjac]

Hi.I am trying to find out of how to copy the country and the data for that country but have problems with the off set. Hope some can help with this.

Thanks in advance

Sincerely

Abjac

[alansidman]

Firstly, for this to work effectively, you need to change your sheet design so that the year and country are in two different columns. There are no blank spaces in either of those columns.

See attached for example and code that will copy and paste to Sheet1 based upon your input. One input at a time.

[abjac]

HI alansidman. Thanks for your answer. I understand what you mean by changing the sheets layout.

I have many countries in the real sheet. Is there a code i can use to change this from what you see to the layout you suggest?

According to your code it works great. But i need more possibilities.
To be able to select more than one country, and the complete year for those countries, or select the months for those countries i have chosen.

I know this is maybe a hard one. But its what i really need.
Some countries will be connected example Nordic countries.
Please have a look no rush, but see if this can be done.
But please start with the code for changing the layout.

Thanks in advance

Sincerely

Abjac

[alansidman]

Here is the first piece of code to clean up your data and put it in a database format.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[abjac]

HI alansidman. Thanks allot.

That works like a charm. I tried in the big sheet and it put it perfect in there. So now its in the data format.

If time could look at the other questions. Would really be great. No rush ok.

Thanks for now.

Sincerely

abjac