copy and paste cells with roll over dates

Hi All,

I have the data for one commodity over different years. I want to copy the data from Each sheet ( i.e., different years) to one mastersheet. My data in Each sheet (for example 2004) looks like:

Date Symbol Contract Month Open High Low Close PCP Volume OI

3-Jun-04 Nickel 23rd July 12 16 5 18 12 8 20
-----------

23-July-04 23 rd July 18 22 7 23 34 7 24

3-Jun-04 31st Aug -- --- --- --- --- -- ---

31-Aug-04 31 st Aug --- --- --- ----- ---- ---- ------


Note: There will be data for different contract months foreach year. In the above example next contract follows 31st Aug. There may be data for the same date for both 23rd July and 31st Aug. With the same format i have data for different years.

Desired Output (master sheet):
Close Price
Date Symbol Contract Name Maturity month Nearby Month Far Month
3-jun-04 Nickel 23 rd July

23 - July-04 Nickel 23 rd July

Next Contract follows

24 - July Nickel 31st Aug

31-Aug Nickel 31st Aug

Next Contract

Note: Starting date for the first contract always starts from the beginning of the month (In case of earlier dates as in the above example it shows that there is no contract available in June but usually these cases are very less number) The rollover contract starts from the next date of the expired contract i.e., Close price for Near Month column. Aug contract closing price starts from 24 th july in this case.

Maturity Month: The closing price of the current trading month. i.e July

Nearby Month: It shows the closing price of the immediate or next month (here if we start from July it will be Aug and so on

Far Month: It shows (Maturity month + 2) closing price i.e., closing price of septmber contract.

In the master sheet the first column which shows, the dates of the trading should follows from each sheet for different years. As there may be duplications of date for different conract. It should pick up only the dates without any duplication. Then i want to copy the data with different contracts from each sheet ( with different years) and creates my master sheet.

Please let me know if you find the above explanation is circular.

Please suggest me for formula or macro which will make this task very effciently as this is beyond my cognitive abilities to have the solution for the same. I have tried VLOOK UP function for this but do not know how to make it dynamic.

With sincere regards,
UpanandaAttachment 112396

Put this macro into the MASTER SHEET module, it run itself each time you bring up the Master Sheet onscreen from another sheet. This will collect the data from all sheets that have "numeric" tab names.

Please Login or Register  to view this content.

The macro thus works on your already existing Master Sheet, the headers that are there will be left there.

Hi JB,

Thanks a lot for the timely help that is working like magic. I do not want duplicate records in the master sheet e.g if you look at when 23 july data ends immediately the 31st Aug should come over the next date 26 th July. So there is no need to have data from 13th July to 23rd July for the 31st Aug as i want the active contract to roll over from the last day of the previous contract.

I have highlighted the cells in the master sheet and attached for your reference.

Hope i am not wrong in the above points

Thanking you so much.

With sincere regards,
Upananda