Andy has attended:
Excel Advanced course
Pivot table
Hi
I'm using a Pivot table and want to know how to open ALL show details in one go into separate worksheets instead of double clicking onto each number in the pivot table. I'm sure we were shown this on the course but can't find any notes on it.
Kind Regards
Andy
RE: Pivot table
Hi Andy,
Thank you for the forum question.
If you want to see all in a new worksheet you just need to double click the grand total which represent all the data you want to show in another sheet.
You can add subtotals for rows and columns and you can have grand totals. If you just double click one value representing a group you will extract only the specific group in a new worksheet.
I hope this can help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Pivot table
Hi Jens
I haven't made myself clear, sorry. I meant to say that I want each individual drill for each number in the pivot table to come up without me having to double click to open a new worksheet each time.
i.e. I have a pivot table with 108 sums for which I need to drill open a new worksheet for each sum and print off... i.e 108 new worksheets. At present I need to double click each sum in the pivot table to open a new worksheet. Is there anyway I can open all new worksheet at once without having to double click each individual sum?
the grand total just opens one new worksheet, I need each worksheet separated.
Regards
Andy
RE: Pivot table
Hi Andy,
Thank you for clarify the question.
There is always a way in Excel.
It can only be done with a macro and I do not know how much knowledge you have with macros.
If you place the code below in the Visual Basic Editor and amend the code to fit your PivotTable it will do the job.
The line "Sub CreatePivotSheets()" will tell Excel that you want to create a macro with the name CreatePivotSheets.
The last line "End Sub" will tell Excel that here will the macro end. In the line "Sheets("Sheet1").Select" you will need the change the name of the sheet in which you have your PivotTable. If your sheet name is January change the line to "Sheets("January").Select".
The next line "For Each Cell In Range("b9:e9")" is a loop and in my example it will run through the cells B9, C9, D9 & E9. You will need to amend the line to define in which range you have the totals.
You will not need to do other changes to the code below.
Sub CreatePivotSheets()
Sheets("Sheet1").Select
For Each Cell In Range("b9:e9")
Cell.ShowDetail = True
Next Cell
End Sub
I hope that this can help you. VBA in Excel is magic and can do what ever we want to do in Excel.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
