Copy data from one sheet to another sheets in the same excel based on date

[shansakhi]

Hi All,

Good Morning.

I require your assistance to build a macro to copy data from one sheet to another sheets in same excel file based on date criteria.


Below is the main file where we have three sets (may be more in actuals) data which we need to segregate to three different sheets based on date criteria.

Below Data is in one sheet. (Please note the column and rows may increase)
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]ABCD123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORG[/TD]
[TD]Dest[/TD]
[TD]Car[/TD]
[TD]PT[/TD]
[TD]FC[/TD]
[TD]Amount[/TD]
[TD]Eff Date[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]AB[/TD]
[TD]HUB[/TD]
[TD]PS[/TD]
[TD]412[/TD]
[TD]23-Sep-14[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]AB[/TD]
[TD]HUB[/TD]
[TD]KS[/TD]
[TD]442[/TD]
[TD]24-Sep-14[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]AB[/TD]
[TD]HUB[/TD]
[TD]LS[/TD]
[TD]482[/TD]
[TD]25-Sep-14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABCD456[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORG[/TD]
[TD]Dest[/TD]
[TD]Car[/TD]
[TD]PT[/TD]
[TD]FC[/TD]
[TD]Amount[/TD]
[TD]Eff Date[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]B4[/TD]
[TD]BB[/TD]
[TD]BUB[/TD]
[TD]PB[/TD]
[TD]400[/TD]
[TD]24-Sep-14[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]B5[/TD]
[TD]BB[/TD]
[TD]BUB[/TD]
[TD]KB[/TD]
[TD]520[/TD]
[TD]25-Sep-14[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]B6[/TD]
[TD]BB[/TD]
[TD]BUB[/TD]
[TD]LB[/TD]
[TD]300[/TD]
[TD]22-Sep-14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABCD789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORG[/TD]
[TD]Dest[/TD]
[TD]Car[/TD]
[TD]PT[/TD]
[TD]FC[/TD]
[TD]Amount[/TD]
[TD]Eff Date[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]B7[/TD]
[TD]CB[/TD]
[TD]CUB[/TD]
[TD]PC[/TD]
[TD]302[/TD]
[TD]21-Sep-14[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD]B8[/TD]
[TD]CB[/TD]
[TD]CUB[/TD]
[TD]KC[/TD]
[TD]102[/TD]
[TD]22-Sep-14[/TD]
[/TR]
[TR]
[TD]A9[/TD]
[TD]B9[/TD]
[TD]CB[/TD]
[TD]CUB[/TD]
[TD]LC[/TD]
[TD]600[/TD]
[TD]20-Sep-14[/TD]
[/TR]
</TBODY>[/TABLE]


We would like to have three dirfferent sheets ABCD123, ABCD456 and ABCD789 (the names may change).
Then based on Eff Date -- if data is availabe from same day onward then we need to copy the same and paste it to respecting sheets.

Considering 23 Sep 2014 is today ...

The data in output sheets would be as below ...

Sheet1 - ABCD123

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]ORG[/TD]
[TD]Dest[/TD]
[TD]Car[/TD]
[TD]PT[/TD]
[TD]FC[/TD]
[TD]Amount[/TD]
[TD]Eff Date[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]AB[/TD]
[TD]HUB[/TD]
[TD]PS[/TD]
[TD]412[/TD]
[TD]23-Sep-14[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]AB[/TD]
[TD]HUB[/TD]
[TD]KS[/TD]
[TD]442[/TD]
[TD]24-Sep-14[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]AB[/TD]
[TD]HUB[/TD]
[TD]LS[/TD]
[TD]482[/TD]
[TD]25-Sep-14[/TD]
[/TR]
</TBODY>[/TABLE]


Sheet2-ABCD456

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]ORG[/TD]
[TD]Dest[/TD]
[TD]Car[/TD]
[TD]PT[/TD]
[TD]FC[/TD]
[TD]Amount[/TD]
[TD]Eff Date[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]B4[/TD]
[TD]BB[/TD]
[TD]BUB[/TD]
[TD]PB[/TD]
[TD]400[/TD]
[TD]24-Sep-14[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]B5[/TD]
[TD]BB[/TD]
[TD]BUB[/TD]
[TD]KB[/TD]
[TD]520[/TD]
[TD]25-Sep-14[/TD]
[/TR]
</TBODY>[/TABLE]


Sheet3-ABCD789

"NO NEW DATA"
(As there is no data available from 23 Sep 14 onward)

Regards,
Shantanu