ExcelGoodies WorldwideUK | USA | Australia | Singapore | Phillipinnes
VBA Code – To split an Excel PIVOT Table Into Multiple Reports
PIVOT tables are a great way to summarize data 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 the 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, uses 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
VBA Solution
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. 'This script will take a PIVOT Table and copy all the data for each item in the filter list
2. 'www.DedicatedExcel.com
5. Sub CopyPivData()
6 .Â
7 .Dim PT As PivotTable
8. Dim PI As PivotItem
9. Dim PI2 As PivotItem
10. Â
11. '1)Worksheet name where PIVOT Table is located
12. MyWs = "Summary PIVOT"
13. '2)PIVOT table name/number, note by default the first one created is PivotTable1
14. MyPIV = "PivotTable1"
15. 3)Field Name that you want to use for breaking out by, i.e. the filter name
16. MyField = "Staff Name"
17. Â
18. Set PT = Worksheets(MyWs).PivotTables(MyPIV)
19. With PT
20. Â
21. For Each PI In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems
27. If Not PI2.Name = PI.Name Then PI2.Visible = False
28 Next PI2
29. Set NewWs = Worksheets.Add
30. NewWs.Name = PI
31. Â
32. 'You will need to amend the range below to copy the correct amount of data for your
33. file
34. Worksheets(MyWs).Range("A3:C15").Copy
35. Â
36. 'This pastes into cell A1 of the new sheet
37. 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.
Check out comprehensive VBA Course here and, learn to automate your Excel reports with ease.
Learn how to write VB Macros in Microsoft Excel with our specialized course on Excel Automation here.
Power BI
SQL
Power Apps
Power Automate
Microsoft Fabrics
Azure Data Engineering
