Business Professionals
Techno-Business Professionals
Power BI | Power Query | Advanced DAX | SQL - Query &
Programming
Microsoft Fabric | Power BI | Power Query | Advanced DAX |
SQL - Query & Programming
Microsoft Power Apps | Microsoft Power Automate
Power BI | Adv. DAX | SQL (Query & Programming) |
VBA | Python | Web Scrapping | API Integration
Power BI | Power Apps | Power Automate |
SQL (Query & Programming)
Power BI | Adv. DAX | Power Apps | Power Automate |
SQL (Query & Programming) | VBA | Python | Web Scrapping | API Integration
Power Apps | Power Automate | SQL | VBA | Python |
Web Scraping | RPA | API Integration
Technology Professionals
Power BI | DAX | SQL | ETL with SSIS | SSAS | VBA | Python
Power BI | SQL | Azure Data Lake | Synapse Analytics |
Data Factory | Databricks | Power Apps | Power Automate |
Azure Analysis Services
Microsoft Fabric | Power BI | SQL | Lakehouse |
Data Factory (Pipelines) | Dataflows Gen2 | KQL | Delta Tables | Power Apps | Power Automate
Power BI | Power Apps | Power Automate | SQL | VBA | Python | API Integration
New
Home | About Us | Contact Us
ExcelGoodies WorldwideUK | USA | Australia | Singapore | Phillipinnes
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
22. Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems
23. PI.Visible = True
24. Â
25. For Each PI2 In
26. 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.
Happy Excelling
Team Excelgoodies
VBA & Python