Hi
I have a sheet that has codes on - these will change for each file. The basic structure of each worksheet will be the same the only difference will be the code that it relates to
I can create worksheets from the pivot table using the PivotTable Analyze >> PivotTable >> Options >> Show Report Filter Pages but this doesn't give me what I require.
I want to create a worksheet for each code on the TB Summary tab. Each worksheet will be named the code it relates to. The actual worksheet will be based on the template layout
How can I achieve this?
I've attached a sample workbook of what I'm trying to achieve
Thanks
Hi Alison,
The file didn't get attached. You need to click 'start upload' after selecting your file, then wait for the grey check mark beside the file size before clicking 'submit reply'.
Mynda
Apologies File attached now
Hi Alison,
Unfortunately, the file doesn't really help me understand why the 'Show report filter pages' tool doesn't work for you (I honestly think this is probably your best option, but I can't tell from the example file). What I can see is the TB summary data is not in a tabular layout, so you couldn't build a PivotTable from it, but that could be fixed with Power Query.
Can you please put some dummy data in the file and show me what you want vs what the PivotTable 'Show report filter pages' tool gives you so I can see the issue?
Mynda
The pivot table solution creates the sheets ie the sheets with the name of the code which is fine what it doesn’t do is use the template worksheet as the basis for each new sheet
what I want is for each sheet it creates to be based on the template worksheet with all the formulas etc being copied with the code in column A being the same code as the sheet name
So the problem is that you can't get a PivotTable to display in the layout shown in the Template sheet. If that's the case then you'd need to write some VBA to create these sheets for you. There's nothing built into Excel that will do it.
That said, I'd have thought you can get a PivotTable that's very close to your Template layout if you use Power Query to unpivot the layout of your Summary data into a Tabular layout suitable for PivotTables.
This PivotTable Profit & Loss report might give you some ideas for building PivotTables with custom subtotals.
Mynda
