Creating a master Data sheet pulling information from each sheet in book

[grini35]

Hello,

I currently have a workbook that is made up of 5 different sheets. Each of these sheets displays unique information about a number of accounts for specific dates.

for instance:

Sheet 1
A B C D E . . .
ID Date sales AP AR
a1 1/1/09 $20 $10 $5
a1 1/1/10 $15 $6 $8
a2 1/1/10 $10 $5 $2
a2 1/1/09 $15 $6 $8
.
.
.
Sheet 2
A B C D E F . . .
ID Inv Date CFO Backlog EBIT
a1 $100 1/1/09 $22 $1 $50
a1 $60 1/1/10 $16 $7 $18
a2 $52 1/1/10 $12 $4 $21
a2 $70 1/1/09 $11 $5 $12
.
.
.

Sheet 3 (unique to itself)
etc.

I want to creat a master sheet that I can then create a pivot table with. The aim would be to have a row for each unique ID and Date with the information from each sheet populated to be examined in whole.

OutPut Sheet (look along these lines)
A B C D E F . . .
ID Date sales AP AR EBIT . . .


One of my biggest concerns is that the amount of data will continue to increase as time goe on, so I was unsure of there being a way to define an array for each sheet to work with.

Would like this report to be automatically generated with a VBA so that it can be generated by anyone in the organization.

Any help is greatly appreciated!!

[Rob342]

Why dont you create 1 master sheet data base with all the data
Then create your pivot tables from that?

Rob

[grini35]

Rob,

I have attempted this but with the column heading being different and the unique identifier being repeated, it has been a process which takes ages to accomplish.

Do you have any suggestion to where I might find a macro to help with said proccess?

Thanks for the reply

[Rob342]

Post a sample copy of your workbook and i'll take a look
I think you can combine all the data into 1 main sheet using a dynamic array for each, then let the pivot table sort it out.

Rob