Macro to pick up values from three different excel sheets at a time and create a list

I need to create a list of production figures for different facilities from a folder saved on the common server.

The folder is titled 'Daily Production Reports' and it has three subfolders titled 'CPF' , 'Manzalai' and 'Makori'. Within each subfolders are folders for the year 2009, 2010 and 2011 which once opened has 12 folders for each month of the year. In each of these folders are the workbooks that contain daily production values for each day of the month separately.

I made a macro to pick up values for the 1st of Jan 2011 from each of these folders for product A and B.
The resulted row has the data in the following order:

1-Jan-2011, CPF Product A value, CPF Product B value, MGP Product A value, MGP Product B value, Makori Product A value, Makori Product B value.

(the comma inserted above is differentiating each cell in the row just for reference)

The macro goes into each of the respective folders and picks up both product values for each facility and inserts it into the same row.

Each row value has the following route:
Row 1 column 1,2,3:Daily Production Reports>CPF>2011>January>DPR_CPF_01_01_2011

Within this sheet, the date is in cell reference: J21
CPF Product A value is in cell: T31
CPF Product B value is in cell:T38

Row 1 column 4,5:
Daily Production Reports>Manzalai>2011>January>DPR_MGP_01_01_2011

Within this sheet, the Manzalai (MGP) Product A value is in cell: R29
Manzalai(MGP) Product B value is in cell: R33

Row 1 column 6, 7:
Daily Production Reports>Makori>2011>January>DPR_Mak_01_01_2011

Within this sheet, the Makori Product A value is in cell: R29
Makori Product B value is in cell: R33

This is the route for the year 2011 and month Jan, I need to do this now for all the months and dates in the 3 years 2009, 2010 and 2011 which would be listed down in order.
For all the other files though the name of the month and year changes but the cell reference remains the same for the values.

The macro I've made so far is as follows:

Please Login or Register  to view this content.

But this macro only picks up values for that one specific date. To do the same for all the files in the Daily Production Reports folder I either need to make the macro more generic? But how is that done? I'm not good with macros and this is my very first time. If there is any other solution to my problem I would be very grateful for your help.

Plus when a solution is given please explain the procedure in detail as Im an amateur in the field.

Hi hyatkhan

Welcome to the Forum....

Please wrap your code in code tags, before the moderators get you...

Click here - Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Cheers