copy data to other sheet by vba to consolidate year/month into single column-date

**QuestAjay** · 2015-02-12, 07:25 AM

Hi,
Please help me to consolidate data from 1st sheet to 2nd sheet such that the first sheet has year, month and days as column and rows and i want to copy this data to 2nd sheet as a single column date.
I have several sheets like this so i want a vba code for the same. I am not able to consolidate the data to one column.
Sample data format:

Code:

1st sheet
            Year     Month       1969        1970              1971           .... so on to 2015
            Day     January 
              1                 data1        data101       data201 ....
              2                 data2        data102       data202.....
            so on to 31 January....

              1      February     data1      data101       data201 ....
              2                   data2      data102       data202.....
          so on to 28 or 29 February...
              1      March         data1      data101       data201 ....
              2                    data2      data102       data202.....
          so on to 31March...
          Till December...

          2nd sheet
            Date                   Data
            1-jan-1969             Data1
            2-jan-1969             Data2
            so on till
            1-jan-2015            Data100001

Kindly help me device a code fr the same as i am unable to do it. Thanks in advance.Sample file is attached.

**Herbds7** · 2015-02-13, 03:21 AM

Excel 2010 with free Power Query and PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
Can be done with oodles of other tools,
but this is the most elegant.
http://805fbd0dee7e8ba6e8e2-24c8ffd6...02_12_15a.xlsx
http://805fbd0dee7e8ba6e8e2-24c8ffd6.../02_12_15a.pdf