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
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.

Check out comprehensive VBA Course here and, learn to automate your Excel reports with ease.

Happy Excelling
Team Excelgoodies
www.Excelgoodies.com

;

One thought on “VBA Code – To split an Excel PIVOT Table into Multiple Reports

  1. spend Time

    I’m not that much of a internet reader to be
    honest but your blogs really nice, keep it up

    I’ll go ahead and bookmark your website to come back later
    on. Cheers

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *