Macro 76: Create New Workbook for Each Report Filter Item
Pivot tables provide an excellent mechanism to parse large data sets into separate files. You can build a
PivotTable report, complete with aggregations and analysis, and then place a field (like Region) into the
report filter. With the report filter, you can select each data item one at a time, and then export the
PivotTable data to a new workbook.
The macro in this section demonstrates how to automatically iterate through all the values in a
report filter and export to a new workbook.
How it works
In the Excel object model, the Report Filter drop-down list is known as the PageField. To print a
PivotTable for each data item in a report filter, the macro needs to loop through the PivotItems
collection of the PageField object. As the macro loops, it must dynamically change the selection in
the report filter, and then export the PivotTable report to a new workbook.
Sub Macro76()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a
PivotTable.” Exit Sub
End If
‘Step 4: Exit if more than one page field
If pt.PageFields.Count > 1 Then
MsgBox “Too many Report Filter Fields. Limit 1.”
Exit Sub
End If
‘Step 5: Start looping through the page field and its pivot items
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
‘Step 6: Change the selection in the report filter
pt.PivotFields(pf.Name).CurrentPage = pi.Name
‘Step 7: Copy the data area to a new workbook
pt.TableRange1.Copy
Workbooks.Add.Worksheets(1).Paste
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:=”C:\Temp\” & pi.Name & “.xlsx”
ActiveWorkbook.Close
Application.DisplayAlerts = True
‘Step 8: Get the next page field item
Next pi
Next pf
End Sub
1. Step 1 declares three variables, pt as the memory container for our PivotTable, pf as a mem-
ory container for our page fields, and pi to hold each pivot item as the macro loops through
the PageField object.
2. The active cell must be inside a PivotTable for this macro to run. The assumption is that when
the cursor is inside a particular PivotTable, we will want to perform the macro action on that
pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found.
The macro does this by using the ActiveCell.PivotTable.Name property to get the
name of the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why we use the On
Error Resume Next statement. This tells Excel to continue with the macro if there is an
error.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt vari-
able is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be
assigned to the variable. If this is the case, the macro notifies the user via a message box, and
then we exit the procedure.
4. Step 4 determines whether there is more than one report filter field. If the count of
PageFields is greater than one, there is more than one report filter. The reason we do this
check is simple. We want to avoid printing reports for filters that just happen to be there.
Without this check, you might wind up printing hundreds of pages. The macro stops and dis-
plays a message box if the field count is greater than 1.
You can remove the one report filter limitation if you need to simply by deleting or com-
menting out Step 4 in the macro.
5. Step 5 starts two loops. The outer loop tells Excel to iterate through all the report filters. The
inner loop tells Excel to loop through all the pivot items in the report filter that currently has
focus.
6. For each pivot item, Step 6 captures the item name and uses it to change the report filter
selection. This effectively alters the PivotTable report to match the pivot item.
7. Step 7 copies TableRange1 of the PivotTable object. TableRange1 is a built-in range
object that points to the range of the main data area for the PivotTable. We then paste the
data to a new workbook and save it. Note that you need to change the save path to one that
works in your environment.
8. Step 8 moves to the next pivot item. After the macro has looped through all pivot items in
the report filter, the macro moves to the next PageField. After all PageFields have
been evaluated, the macro ends.
How to use it
You can implement this kind of a macro in a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
