This guide will show you how to take an Excel PIVOT table and by using the VBA script provided you will be able to generate a new worksheet for each of the values within your Excel PIVOT table filter…
PIVOT tables are a great way to summarise data in Excel but sometimes it is important to be able to split a PIVOT table out, generating multiple reports based off all the items within the filter:
For example, you might have a manager who wants a sales report for all her sales staff. It’s a basic report; she just wants to look at volume of sales by month for each staff member.
A PIVOT table is an excellent solution to this problem because it’s quick to produce, easy to update and allows the manager to see all her data on one screen, using the filter to select the staff member of interest at the top.
However upon reviewing she now thinks that it would be useful to also have a copy of each of the staff member’s sales volumes individually. That way she can send a staff member their own sales report if she wants, use them in reviews etc.
That’s all well and fine when you only have a handful of staff but what happens if you have 40 staff in your team…that is potentially a lot of copying and pasting, especially if this is every month!
Rather than creating multiple PIVOT tables or multiple reports we can keep things efficient by using some VBA to resolve our problem. This VBA works by looping through each item in the filter and copying the data found to a new worksheet, for neatness the code then labels that worksheet using the filter item name so that you can locate it easily, here is the script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
'This script will take a PIVOT Table and copy all the data for each item in the filter list 'www.DedicatedExcel.com Sub CopyPivData() Dim PT As PivotTable Dim PI As PivotItem Dim PI2 As PivotItem '1)Worksheet name where PIVOT Table is located MyWs = "Summary PIVOT" '2)PIVOT table name/number, note by default the first one created is PivotTable1 MyPIV = "PivotTable1" '3)Field Name that you want to use for breaking out by, i.e. the filter name MyField = "Staff Name" Set PT = Worksheets(MyWs).PivotTables(MyPIV) With PT For Each PI In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems PI.Visible = True For Each PI2 In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems If Not PI2.Name = PI.Name Then PI2.Visible = False Next PI2 Set NewWs = Worksheets.Add NewWs.Name = PI 'You will need to amend the range below to copy the correct amount of data for your file Worksheets(MyWs).Range("A3:C15").Copy 'This pastes into cell A1 of the new sheet NewWs.Range("A1").Select ActiveSheet.Paste Next PI End With End Sub
This solution can be linked to a button, allowing the manager to break/split out the PIVOT table as and when they need, or you can just run the script before-hand to generate all the individual reports to send to the manager.
Adapting the script
This code is very useful and easy to tailor for different situations.
For example rather than copying the data to a new worksheet you could copy the data to a new workbook, creating a new file for each PIVOT item. You can also deploy this code within an Excel dashboard, one example might be to include a button next to a PIVOT chart which then breaks out all the data for the user to review.
Hopefully you’ll find this script effective and easy to implement, please feel free to leave a comment if you have found an use for this script, it’s always good to hear the different ways Excel
analysts use code.
Download a copy of this script working in Excel, with sample data.