Macro to fill a table in a certain format

[conquistador]

Hello,

I would like to ask you about how to type a code which will work just like someone sorting, filtering certain info and then pasting it to a table with a fixed format. For example let the table below be the raw data we have:

Year Month Product Type Transaction Type Quantity Unit Value in Dollars
2011 1 A Production 500 Kg 100
2012 2 B Production 1000 Kg 100
2013 4 B Foreign Sales 2000 Kg 300
2012 5 A Foreign Sales 1000 Kg 400
2012 6 C Domestic Sales 200 Kg 200
2010 1 D Domestic Sales 200 Kg 300
2012 2 E Production 100 Kg 500
2015 3 F Domestic Sales 5000 Kg 1000


The macro should return a table like this with the appropriate data are entered:

2010 2011 2012 2013 2014 2015
Production $
Kg
Domestic Sales $
Kg
Foreign Sales $
Kg

Please note while the raw data is not chronologically ordered the output table should be so. By the way is it possible for the macro to provide the user with an option to select the time period the user interested in as well as the product type. A message box which asks if the user is interested in one or multiple product types and also if the user wants only a certain period to be taken into consideration. If none of these are the case, then the macro will fill the table for the complete span of the info.

[Kaper]

Looking at your data and desired output, probably you do not need a macro, but could use just Pivot Table. Are you familiar with this built-in excel tool?

[Kaper]

PS. Have a look at the sample

[conquistador]

yes I am familiar with pivot tables but I need that certain data so that another excel sheet uses it for formulas and calculations. in fact with a little handwork a pivot table gets the job done but I am trying to speed things up with as little mistakes as possible.

[conquistador]

Hello and thanks for your reply.

You are right pivot tables come real handy for such tasks but I need that certain information extracted from the raw data so that it can directly go into a report easily and more accurately.
Can you help me with that?

[Kaper]

I really see no point to do this from the scratch, while pivot table has it all.
Of course one can built a kind of "Pivot Table Director" - for instance in UserForm and select period, products, etc in it. But then, IMO, easiest and most accurate way is still to produce pivot table out of raw data just using this userform content to filter output.