create summary file from multiple files

[anis]

greetings,

i'm on a search of an efficient way of creating a summary files from another 8 files. First let me explain:

1) Source files will be named - Jan_Sum.xls, Feb_Sum.xls, Mac_Sum.xls, etc
in the My Document folder
2) Output file(summary) will be named - Jan_Summary.xls, Feb_Summary.xls, etc. and will be saved into My Document folder as well.

Here's wut the data would look like:

DEC file:

Book1
	B	C	D	E	F
2	FRUIT
3	type		Mar-06	Apr-06	May-06
4	orange	Demand	76	45	80
5		Capacity	100	100	100
6		Loading %	76%	45%	80%
7
8	banana	Demand	10	13	40
9		Capacity	50	50	60
10		Loading %	20%	26%	67%
11
Dec

JAN file:

Book1
	B	C	D	E	F
14	FRUIT
15	type		Apr-06	May-06	Jun-06
16	orange	Demand	46	70	57
17		Capacity	100	100	100
18		Loading %	46%	70%	57%
19
20	banana	Demand	5	32	40
21		Capacity	50	50	60
22		Loading %	10%	64%	67%
23
Dec

These are the sample input files

 

[anis]

here's the sample output file

Sample summary chart for JAN_SUMMARY

Book1
	B	C	D	E	F	G	H	I	J
27		Nov-05	Dec-05	Jan-06	Feb-06	Mar-06	Apr-06	May-06	Jun-06
28	Orange
29	May	xx	xx	xx	xx	xx	xx	xx	xx
30	Jun	xx	xx	xx	xx	xx	xx	xx	xx
31	Jul	xx	xx	xx	xx	xx	xx	xx	xx
32	Aug	xx	xx	xx	xx	xx	xx	xx	xx
33	Sep		xx	xx	xx	xx	xx	xx	xx
34	Oct			xx	xx	xx	xx	xx	xx
35	Nov				xx	xx	xx	xx	xx
36	Dec					76	45	80	xx
37	Jan						46	70	57
38
39	Banana
40	May	xx	xx	xx	xx	xx	xx	xx	xx
41	Jun	xx	xx	xx	xx	xx	xx	xx	xx
42	Jul	xx	xx	xx	xx	xx	xx	xx	xx
43	Aug	xx	xx	xx	xx	xx	xx	xx	xx
44	Sep		xx	xx	xx	xx	xx	xx	xx
45	Oct			xx	xx	xx	xx	xx	xx
46	Nov				xx	xx	xx	xx	xx
47	Dec					10	13	40	xx
48	Jan						5	32	40
Dec

OK, so here's go the logic that i've come up wif. the summary file will be created when the source file for that month is created. i've completed the code for the template and create new file save the file and everything. but now i'm a bit lost in the logic for copying the values of demand from every other file. i'm wondering is the logic i have here would be an efficient one to be coded or there's more efficient way of producing the same output?

- activate main source file ( i.e if user is to create a JanSummary file, Jan source file would be the main source file)
- search for the particular fruit in col B
- check if the 1st month of the chart = or > 1st month of the output chart
Yes --> copy data from 1st month, activate output file, search where the month's col is, set the col to a variable (eg: CL). search for the particular fruit in col A, go to the row of the source file's month(obtain from the source file name), set the row of the source file's month in the range of the particular fruit to a variable (eg: RW) . Paste the copied values to (RW&CL)

No --> get 1st month of the output chart, check in the input file for the month, get the col of the month. copy the values for the fruit's demand starting from the month till last data. activate output file, search for the particular fruit in col A, go to the row of the source file's month(obtain from the source file name), set the row of the source file's month in the range of the particular fruit to a variable (eg: RW) . Paste the copied values to (RW,2)

- loop for the previous 8 months file. (i.e if Jan is main source file, loop for Dec until May source files)

 

[anis]

hmph...confused as well? do help~~

i think that i'm thinking too much now~~~
hope there's sumone who can help me sort this a bit and makes it more clearer cos im definitely confused with my own logic at this moment.
THANX a lot in advance...

best regards,

 

[anis]

struggling hoping praying...

anyone???