grini35
10-17-2011, 10:41 AM
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!!
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!!