If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create multiple sheets and pivot tables from single source
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. |
Thread Tools | |
Display Modes | |
|
|