D
dave
Hi all, sorry for the length of this post, I'll try to keep things as
simple as possible. I've been given the task of maintaining a
spreadsheet with all of the data showing in pivot tables from a single
source which would be updated every month.
The problem arises when it comes to the shear scale of the worksheet
required, in simple terms the main worksheet shows the following -
* Total sales by company types, regions and product types. (4 pivot
tables in total)
* Average sales by company types, regions and product types. (3 pivot
tables in total)
* Total sales by product name sorted by name and total (2 x 3 pivot
tables)
I then have 9 more copies of this sheet which filter the results as
such -
* Region. (3 regions so 3 sheets)
* Region + company type (3 regions, 2 company types so 6 sheets).
The lazy way that I could produce this is by creating the first sheet
and then just make 9 copies of it and change the Page Fields according
to the relevant region and/or company type but this is arduous and
doesn't really allow much flexibility.
Also the first time I created this report I based all of the pivots
off of a single pivot whose data source was fixed coordinates and of
course now I want to add data I've had to alter them, in fact now to a
data name. The problem is that all of the pivots 'lost' the fact that
they refer to the original pivot so I've had to go through and change
their source manually.
Can anyone think of a way in which I could automatically create all of
my worksheets either through VB or another way?
Thanks in advance.
simple as possible. I've been given the task of maintaining a
spreadsheet with all of the data showing in pivot tables from a single
source which would be updated every month.
The problem arises when it comes to the shear scale of the worksheet
required, in simple terms the main worksheet shows the following -
* Total sales by company types, regions and product types. (4 pivot
tables in total)
* Average sales by company types, regions and product types. (3 pivot
tables in total)
* Total sales by product name sorted by name and total (2 x 3 pivot
tables)
I then have 9 more copies of this sheet which filter the results as
such -
* Region. (3 regions so 3 sheets)
* Region + company type (3 regions, 2 company types so 6 sheets).
The lazy way that I could produce this is by creating the first sheet
and then just make 9 copies of it and change the Page Fields according
to the relevant region and/or company type but this is arduous and
doesn't really allow much flexibility.
Also the first time I created this report I based all of the pivots
off of a single pivot whose data source was fixed coordinates and of
course now I want to add data I've had to alter them, in fact now to a
data name. The problem is that all of the pivots 'lost' the fact that
they refer to the original pivot so I've had to go through and change
their source manually.
Can anyone think of a way in which I could automatically create all of
my worksheets either through VB or another way?
Thanks in advance.