How the macro works

It’s important to know that each PivotTable object is a child of the worksheet it sits in. The macro has to loop through the worksheets in a workbook first, and then loop through the pivot tables in each worksheet. This macro does just that — loops through the worksheets, and then loops through the pivot tables. During each loop, the macro refreshes the pivot table.

Sub Macro1()

'Step 1: Declare your variables
    Dim ws As Worksheet
    Dim pt As PivotTable

'Step 2: Loop through each sheet in workbook
    For Each ws In ThisWorkbook.Worksheets

'Step 3: Loop through each pivot table
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

End Sub

Step 1 first declares an object called ws. This step creates a memory container for each worksheet you loop through. It also declares an object called pt, which holds each pivot table the macro loops through.

Step 2 starts the looping, telling Excel you want to evaluate all worksheets in this workbook. Note that you're using ThisWorkbook instead of ActiveWorkbook. The ThisWorkbook object refers to the workbook that contains the code. The ActiveWorkbook object refers to the currently active workbook. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, you don’t want to risk refreshing pivot tables in other workbooks, so you use ThisWorkbook.

Step 3 loops through all pivot tables in each worksheet, and then triggers the RefreshTable method. After all pivot tables have been refreshed, you move to the next sheet. After all sheets have been evaluated, the macro ends.

As an alternative method for refreshing all pivot tables in the workbook, you can use ThisWorkbook.RefreshAll. This method refreshes all pivot tables in the workbook — but it also refreshes all query tables. If you have data tables that are connected to an external source or the web, they will be affected by the RefreshAll method. If this is not a concern, you can simply enter ThisWorkbook.RefreshAll into a standard module.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

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

How the macro works

When you create a PivotTable Object variable, you expose the pivot table’s properties, such as its name, location, and cache index. In this macro, you loop through each pivot table in the workbook and extract specific properties into a new worksheet.

Because each PivotTable object is a child of the worksheet it sits in, you have to first loop through the worksheets in a workbook, and then loop through the pivot tables in each worksheet.

Take a moment to walk through the steps of this macro in detail:

Sub Macro1()

'Step 1: Declare your variables
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim MyCell As Range

'Step 2: Add a new sheet with column headers
    Worksheets.Add
    Range("A1:F1") = Array("Pivot Name", "Worksheet", _
                           "Location", "Cache Index", _
                           "Source Data Location", _
                           "Row Count")

'Step 3: Start cursor at cell A2 setting the anchor here
    Set MyCell = ActiveSheet.Range("A2")

'Step 4: Loop through each sheet in workbook
    For Each ws In Worksheets

'Step 5: Loop through each pivot table
       For Each pt In ws.PivotTables
       MyCell.Offset(0, 0) = pt.Name
       MyCell.Offset(0, 1) = pt.Parent.Name
       MyRange.Offset(0, 2) = pt.TableRange2.Address
       MyRange.Offset(0, 3) = pt.CacheIndex
       MyRange.Offset(0, 4) = Application.ConvertFormula _
       (pt.PivotCache.SourceData, xlR1C1, xlA1)
       MyRange.Offset(0, 5) = pt.PivotCache.RecordCount
'Step 6: Move cursor down one row and set a new anchor
       Set MyRange = MyRange.Offset(1, 0)

'Step 7: Work through all pivot tables and worksheets
       Next pt
    Next ws

'Step 8: Size columns to fit
    ActiveSheet.Cells.EntireColumn.AutoFit

End Sub

Step 1 declares an object called ws. This step creates a memory container for each worksheet you loop through. You then declare an object called pt, which holds each pivot table you loop through. Finally, you create a range variable called MyCell, which acts as your cursor as you fill in the inventory summary.

Step 2 creates a new worksheet and adds column headings that range from A1 to F1. Note that you can add column heading using a simple array that contains your header labels. This new worksheet remains your active sheet from here on out.

Just as you would manually place your cursor into a cell if you were to start typing data, Step 3 places the MyCell cursor in cell A2 of the active sheet. This step establishes your anchor point, allowing you to navigate from here.

Throughout the macro, you see the use of the Offset property. The Offset property allows you to move a cursor x rows and x columns from an anchor point. For instance, Range(A2).Offset(0,1) would move the cursor one column to the right. If you wanted to move the cursor one row down, you would enter Range(A2).Offset(1, 0).

In the macro, you navigate by using Offset on MyCell. For example, MyCell.Offset(0,4) would move the cursor four columns to the right of the anchor cell. After the cursor is in place, you can enter data.

Step 4 starts the looping, telling Excel you want to evaluate all worksheets in this workbook.

Step 5 loops through all pivot tables in each worksheet. For each pivot table found, it extracts the appropriate property and fills in the table based on the cursor position (see Step 3).

You are using six pivot table properties: Name, Parent.Range, TableRange2.Address, CacheIndex, PivotCache.SourceData, and PivotCache.Recordcount. The Name property returns the name of the pivot table. The Parent.Range property gives you the sheet where the pivot table resides. The TableRange2.Address property returns the range that the PivotTable object sits in.

The CacheIndex property returns the index number of the pivot cache for the pivot table. A pivot cache is a memory container that stores all the data for a pivot table. When you create a new pivot table, Excel takes a snapshot of the source data and creates a pivot cache. Each time you refresh a pivot table, Excel goes back to the source data and takes another snapshot, thereby refreshing the pivot cache. Each pivot cache has a SourceData property that identifies the location of the data used to create the pivot cache. The PivotCache.SourceData property tells us which range will be called on when you refresh the pivot table. You can also pull out the record count of the source data by using the PivotCache.Recordcount property.

In Step 6, each time the macro encounters a new pivot table, it moves the MyCell cursor down a row, effectively starting a new row for each pivot table.

Step 7 tells Excel to loop back to iterate through all pivot tables and all worksheets. After all pivot tables have been evaluated, you move to the next sheet. After all sheets have been evaluated, the macro moves to the last step.

Step 8 finishes with a little formatting, sizing the columns to fit the data.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

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

How the macro works

Ideally, the name of the each data item matches the field name from your source data set (the original source data used to create the pivot table). Unfortunately, pivot tables won’t allow you to name a data field with the same name as the source data field. The workaround for this limitation is to add a space to the end of the field name. Excel considers the field name (with a space) to be different from the source data field name. And the readers of your spreadsheet don't notice the space after the name.

The macro utilizes this workaround to rename your data fields. It loops through each data field in the pivot table, and then resets each header to match its respective field in the source data plus a space character.

Sub Macro1()

'Step 1: Declare your variables
    Dim pt As PivotTable
    Dim pf As PivotField

'Step 2: Point to the pivot table in the active cell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3: Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside a pivot table."
    Exit Sub
    End If

'Step 4: Loop through all pivot fields and adjust titles
    For Each pf In pt.DataFields
        pf.Caption = pf.SourceName & Chr(160)
    Next pf

End Sub

Step 1 declares two object variables. It uses pt as the memory container for your pivot table, and pf as a memory container for the data fields. This step allows the macro to loop through all the data fields in the pivot table.

The macro is designed so that you infer the active pivot table based on the active cell. In other words, the active cell must be inside a pivot table for this macro to run. The assumption is that when the cursor is inside a particular pivot table, you want to perform the macro action on that pivot table.

Step 2 sets the pt variable to the name of the pivot table on which the active cell is found. You do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.

If the active cell is not inside a pivot table, an error is thrown. For this reason, you use the On Error Resume Next statement to tell Excel to continue with the macro if there is an error.

In Step 3, you check to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a pivot table, thus no pivot table could be assigned to the variable. If this is the case, you use a message box to tell the user, and then you exit the procedure.

If the macro reaches Step 4, it has successfully pointed to a pivot table. The macro uses a For Each statement to iterate through each data field. Each time a new pivot field is selected, the macro changes the field name by setting the Caption property to match the field’s SourceName. The SourceName property returns the name of the matching field in the original source data.

To that name, the macro concatenates a nonbreaking space character: Chr(160).

Every character has an underlying ASCII code, similar to a serial number. For instance, the lowercase letter a has an ASCII code of 97. The lowercase letter c has an ASCII code of 99. Likewise, invisible characters such as the space have a code. You can use invisible characters in your macro by passing their code through the CHR function.

After the name has been changed, the macro moves to the next data field. After all the data fields have been evaluated, the macro ends.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

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

How the macro works

This macro loops through each data field in the pivot table and changes the Function property to xlSum. You can alter this macro to use any calculation choice: xlCount, xlAverage, xlMin, xlMax, and so on. When you go into the code window and type pf.Function =, you see a drop-down list with your choices, as shown in Figure 8-2.

Sub Macro1()

'Step 1: Declare your variables
    Dim pt As PivotTable
    Dim pf As PivotField

'Step 2: Point to the pivot table in the active cell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3: Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside a pivot table."
    Exit Sub
    End If

'Step 4: Loop through all pivot fields and apply SUM
    For Each pf In pt.DataFields
        pf.Function = xlSum
    Next pf

End Sub

Step 1 declares two object variables. It uses pt as the memory container for the pivot table and pf as a memory container for the data fields. This step allows you to loop through all the data fields in the pivot table.

This macro is designed so that you infer the active pivot table based on the active cell. The active cell must be inside a pivot table for this macro to run. The assumption is that when the cursor is inside a particular pivot table, you want to perform the macro action on that pivot.

Step 2 sets the pt variable to the name of the pivot table on which the active cell is found. You do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.

If the active cell is not inside a pivot table, an error is thrown. For this reason, you use the On Error Resume Next statement to tell Excel to continue with the macro if there is an error.

Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a pivot table, thus no pivot table could be assigned to the variable. If this is the case, you use a message box to tell the user, and then you exit the procedure.

If the macro has reached Step 4, it has successfully pointed to a pivot table. It uses a For Each statement to iterate through each data field. Each time a new pivot field is selected, the macro alters the Function property to set the calculation used by the field. In this case, you're setting all the data fields in the pivot table to Sum.

After the function has been changed, you move to the next data field. After all the data fields have been evaluated, the macro ends.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

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

How the macro works

Before running this code, you want to make sure that

• The source data for your pivot table is accessible. The macro needs to see it to capture the correct number formatting.
• The source data is appropriately formatted. Money fields are formatted as currency, value fields are formatted as numbers, and so on.

This macro uses the PivotTable SourceData property to find the location of the source data. It then loops through each column in the source, capturing the header name and the number format of the first value under each column. After it has that information, the macro determines whether any data fields match the evaluated column. If it finds a match, the number formatting is applied to that data field.

Sub Macro1()

'Step 1: Declare your variables
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim SrcRange As Range
    Dim strFormat As String
    Dim strLabel As String
    Dim i As Integer

'Step 2: Point to the pivot table in the active cell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3: Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside a pivot table."
    Exit Sub
    End If

'Step 4: Capture the source range
    Set SrcRange = _
    Range(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))

'Step 5: Start looping through the columns in source range
    For i = 1 To SrcRange.Columns.Count

'Step 6: Trap the source column name and number format
        strLabel = SrcRange.Cells(1, i).Value
        strFormat = SrcRange.Cells(2, i).NumberFormat

'Step 7: Loop through fields in the pivot table data area
        For Each pf In pt.DataFields

'Step 8: Check for match on SourceName then apply format
            If pf.SourceName = strLabel Then
            pf.NumberFormat = strFormat
            End If
      Next pf
    Next i

End Sub

Step 1 declares six variables. It uses pt as the memory container for your pivot table and pf as a memory container for your data fields. The SrcRange variable holds the data range for the source data. The strFormat and strLabel variables are both text string variables used to hold the source column label and number formatting, respectively. The i variable serves as a counter, helping you enumerate through the columns of the source data range.

The active cell must be inside a pivot table for this macro to run. The assumption is that when the cursor is inside a particular pivot table, you want to perform the macro action on that pivot.

Step 2 sets the pt variable to the name of the pivot table on which the active cell is found. You do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.

If the active cell is not inside a pivot table, an error is thrown. For this reason, the macro uses the On Error Resume Next statement to tell Excel to continue with the macro if there is an error.

Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a pivot table, thus no pivot table could be assigned to the variable. If this is the case, you use a message box to tell the user, and then you exit the procedure.

If the macro reaches Step 4, it has successfully pointed to a pivot table. You immediately fill your SrcRange Object variable with the pivot table’s source data range.

All pivot tables have a SourceData property that points to the address of its source. Unfortunately, the address is stored in the R1C1 reference style, like this: 'Raw Data'!R3C1:R59470C14. Range objects cannot use the R1C1 style, so you need to convert the address to 'Raw Data'!$A$3:$N$59470.

The fix is simple. You simply pass the SourceData property through the Application.ConvertFormula function, which converts ranges to and from the R1C1 reference style.

After the range is captured, the macro starts looping through the columns in the source range. In this case, you manage the looping by using the i integer as an index number for the columns in the source range. You start the index number at 1 and end it at the maximum number of rows in the source range.

As the macro loops through the columns in the source range, you capture the column header label and the column format. You do this action with the aid of the Cells item. The Cells item gives you a handy way of selecting ranges through code. It requires only relative row and column positions as parameters. Cells(1,1) translates to row 1, column 1 (or the header row of the first column). Cells(2, 1) translates to row 2, column 1 (or the first value in the first column).

strLabel is filled by the header label taken from row 1 of the selected column. strFormat is filled with the number formatting from row 2 of the selected column that is selected.

At this point, the macro has connected with the pivot table’s source data and captured the first column name and number formatting for that column. In Step 7, it starts looping through the data fields in the pivot table.

Step 8 simply compares each data field to see whether its source matches the name in strLabel. If it does, this step will set the number formatting of the pivot field to the same format as the source data field.

After all data fields have been evaluated, the macro increments i to the next column in the source range. After all columns have been evaluated, the macro ends.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code.

How the macro works

The macro in this section resets the sorting on all data fields, ensuring that any new data snaps into place. You should run the macro each time you refresh your pivot table. In the code, you enumerate through each data field in the pivot table, sorting each one as you go.

Sub Macro1()

'Step 1: Declare your variables
    Dim pt As PivotTable
    Dim pf As PivotField

'Step 2: Point to the pivot table in the active cell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3: Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside a pivot table."
    Exit Sub
    End If

'Step 4: Loop through all pivot fields and sort
    For Each pf In pt.PivotFields
        pf.AutoSort xlAscending, pf.Name
    Next pf

End Sub

Step 1 declares two object variables: pt is the memory container for the pivot table and pf is the memory container for your data fields. This step allows the macro to loop through all the data fields in the pivot table.

The active cell must be inside a pivot table for the macro to run. The assumption is that when the cursor is inside a particular pivot table, you want to perform the macro action on that pivot.

In Step 2, you set the pt variable to the name of the pivot table on which the active cell is found. You do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.

If the active cell is not inside a pivot table, an error is thrown. For this reason, you use the On Error Resume Next statement to tell Excel to continue with the macro if there is an error.

Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a pivot table, thus no pivot table could be assigned to the variable. If this is the case, the macro displays a message box to notify the user and then exits the procedure.

Finally, in Step 4, you use a For Each statement to iterate through each pivot field. Each time a new pivot field is selected, you use the AutoSort method to reset the automatic sorting rules for the field. In this case, you're sorting all fields in ascending order. After all the data fields have been evaluated, the macro ends.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

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

How the macro works

You can automate the custom sorting of your fields by using the Position property of the PivotItems object. With the Position property, you can assign a position number that specifies the order in which you would like see each pivot item.

In this example code, you first point to the Region pivot field in the Pvt1 PivotTable. Then you list each item along with the position number indicating the customer sort order you need:

Sub Macro1()

With Sheets("Sheet1").PivotTables("Pvt1").PivotFields _
("Region ")
     .PivotItems("West").Position = 1
     .PivotItems("North").Position = 2
     .PivotItems("South").Position = 3

End With

End Sub

The other solution is to set up a custom sort list, which is a defined list that is stored in your instance of Excel. To create a custom sort list, choose File  ⇒  Options  ⇒  Advanced, and then click the Edit Custom Lists button. Here, you can type West, North, South in the List Entries box and choose the Add button. After setting up a custom list, Excel will realize that the Region data items in your PivotTable match a custom list and will sort the field to match your custom list.

As brilliant as this option is, custom lists do not travel with your workbook. So a macro helps when it's impractical to expect your clients or team members to set up their own custom sort lists.

How to use the macro

You can implement this kind of a macro in a standard module:

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

How the macro works

The PivotTable object exposes several properties that allow you (the developer) to restrict different features and components of a pivot table:

• EnableWizard: Setting this property to False disables the pivot table Tools context menu that normally activates when clicking inside a pivot table.
• EnableDrilldown: Setting this property to False prevents users from getting to detailed data by double-clicking a data field.
• EnableFieldList: Setting this property to False prevents users from activating the field list or moving pivot fields around.
• EnableFieldDialog: Setting this property to False disables the users' ability to alter the pivot field through the Value Field Settings dialog box.
• PivotCache.EnableRefresh: Setting this property to False disables the ability to refresh the pivot table.

You can set any or all these properties independently to True or False. In this macro, you apply all the restrictions to the target pivot table.

Sub Macro1()

'Step 1: Declare your variables
    Dim pt As PivotTable

'Step 2: Point to the pivot table in the active cell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3: Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside a pivot table."
    Exit Sub
    End If

'Step 4: Apply pivot table restrictions
    With pt
     .EnableWizard = False
     .EnableDrilldown = False
     .EnableFieldList = False
     .EnableFieldDialog = False
     .PivotCache.EnableRefresh = False
    End With

End Sub

Step 1 declares the pt PivotTable Object variable that serves as the memory container for your pivot table.

Step 2 sets the pt variable to the name of the pivot table in which the active cell is found. You do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.

Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a pivot table, thus no pivot table could be assigned to the variable. If this is the case, you use a message box to tell the user, and then you exit the procedure.

In the last step of the macro, you are applying all pivot table restrictions.

How to use the macro

You can implement this kind of a macro in a standard module:

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

How the macro works

The PivotField object exposes several properties that allow you (the developer) to restrict different features and components of a pivot table:

• DragToPage: Setting this property to False prevents users from dragging any pivot field into the Report Filter area of the pivot table.
• DragToRow: Setting this property to False prevents users from dragging any pivot field into the Row area of the pivot table.
• DragToColumn: Setting this property to False prevents users from dragging any pivot field into the Column area of the pivot table.
• DragToData: Setting this property to False prevents users from dragging any pivot field into the Data area of the pivot table.
• DragToHide: Setting this property to False prevents users from dragging pivot fields off the pivot table. It also prevents the use of the right-click menu to hide or remove pivot fields.
• EnableItemSelection: Setting this property to False disables the drop-down lists on each pivot field.

You can set any or all these properties independently to True or False. In this macro, you apply all the restrictions to the target pivot table.

Sub Macro1()

'Step 1: Declare your variables
    Dim pt As PivotTable
    Dim pf As PivotField

'Step 2: Point to the pivot table in the active cell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3: Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside a pivot table."
    Exit Sub
    End If

'Step 4: Apply pivot field restrictions
    For Each pf In pt.PivotFields
        pf.EnableItemSelection = False
        pf.DragToPage = False
        pf.DragToRow = False
        pf.DragToColumn = False
        pf.DragToData = False
        pf.DragToHide = False
    Next pf

End Sub

Step 1 declares two object variables: pt is the memory container for your pivot table and pf is the memory container for your pivot fields. This step allows us to loop through all the pivot fields in the pivot table.

Set the pt variable to name of the pivot table on which the active cell is found by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.

Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not in a pivot table, thus no pivot table could be assigned to the variable. If this is the case, the macro notifies the user via a message box and then exits the procedure.

Step 4 of the macro uses a For Each statement to iterate through each pivot field. Each time a new pivot field is selected, you apply all your pivot field restrictions.

How to use the macro

You can implement this kind of a macro in a standard module:

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

How the macro works

The basic premise of this macro is simple. When the user clicks for details, outputting a drill-down sheet, the macro renames the output sheet so that the first 10 characters are PivotDrill. Then before the workbook closes, the macro finds any sheet that starts with PivotDrill and deletes it.

The implementation does get a bit tricky because you have two pieces of code. One piece goes in the Worksheet_BeforeDoubleClick event, and the other piece goes into the Workbook_BeforeClose event.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'Step 1: Declare your variables
    Dim pt As String

'Step 2: Exit if double-click did not occur on a pivot table
    On Error Resume Next
    If IsEmpty(Target) And ActiveCell.PivotField.Name <> "" Then
    Cancel = True
    Exit Sub
    End If

'Step 3: Set the PivotTable object
    pt = ActiveSheet.Range(ActiveCell.Address).PivotTable

'Step 4: If Drilldowns are enabled, drill down
    If ActiveSheet.PivotTables(pt).EnableDrilldown Then
         Selection.ShowDetail = True

        ActiveSheet.Name = _
        Replace(ActiveSheet.Name, "Sheet", "PivotDrill")
    End If

End Sub

Step 1 starts by creating the pt Object variable for your pivot table.

Step 2 checks the double-clicked cell. If the cell is not associated with any pivot table, you cancel the double-click event.

If a pivot table is indeed associated with a cell, Step 3 fills the pt variable with the pivot table.

Step 4 checks the EnableDrillDown property. If it is enabled, you trigger the ShowDetail method. This outputs the drill-down details to a new worksheet.

The macro follows the output and uses the Replace function to rename the output sheet so that the first 10 characters are PivotDrill. The Replace function replaces certain text in an expression with other text. In this case, you replace the word Sheet with PivotDrill by using Replace(ActiveSheet.Name, “Sheet”, “PivotDrill”).

Sheet1 becomes PivotDrill1; Sheet12 becomes PivotDrill12, and so on.

Next, the macro sets up the Worksheet_BeforeDoubleClick event. As the name suggests, this code runs when the workbook closes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 5: Declare your variables
    Dim ws As Worksheet

'Step 6: Loop through worksheets
    For Each ws In ThisWorkbook.Worksheets

'Step 7: Delete any sheet that starts with PivotDrill
        If Left(ws.Name, 10) = "PivotDrill" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws

End Sub

Step 5 declares the ws Worksheet variable, which holds worksheet objects as you loop through the workbook.

Step 6 starts the looping, telling Excel that you want to evaluate all worksheets in this workbook.

In the last step, you evaluate the name of the sheet that has focus in the loop. If the left 10 characters of that sheet name are PivotDrill, you delete the worksheet. After all the sheets have been evaluated, all drill-down sheets have been cleaned up and the macro ends.

How to use the macro

To implement the first part of the macro, you need to copy and paste it into the Worksheet_BeforeDoubleClick event code window. Placing the macro here allows it to run each time you double-click the sheet:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click the sheet in which you want to trigger the code.
4. In the Event drop-down list box, select the BeforeDoubleClick event (see Figure 8-3).
5. Type or paste the code.

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro here allows it to run each time you try to close the workbook:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click ThisWorkbook.
4. In the Event drop-down list, select the BeforeClose event (see Figure 8-4).
5. Type or paste the code.

How the macro works

In the Excel object model, the Report Filter drop-down list is known as PageField. To print a pivot table for each data item in a report filter, you need to loop through the PivotItems collection of the PageField object. As you loop, you dynamically change the selection in the report filter, and then use the ActiveSheet.PrintOut method to print the target range.

Sub Macro1()

'Step 1: Declare your variables
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

'Step 2: Point to the pivot table in the active cell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3: Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside a pivot table."
    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: Set print area and print
         ActiveSheet.PageSetup.PrintArea = pt.TableRange2.Address
         ActiveSheet.PrintOut Copies:=1

'Step 8: Get the next page field item
        Next pi
    Next pf

End Sub

Step 1 declares three variables: pt is the memory container for your pivot table, pf is a memory container for your page fields, and pi holds each pivot item as you loop through the PageField object.

The active cell must be inside a pivot table for this macro to run. The assumption is that when the cursor is inside a particular pivot table, you want to perform the macro action on that pivot.

Step 2 sets the pt variable to the name of the pivot table on which the active cell is found by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.

If the active cell is not inside a pivot table, the macro throws an error. You use the On Error Resume Next statement to tell Excel to continue with the macro if there is an error.

Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not in a pivot table, thus no pivot table could be assigned to the variable. If this is the case, a message box notifies the user and you exit the procedure.

Step 4 determines whether there is more than one report filter field. (If the count of PageFields is greater than 1, there is more than one report filter.) You do this check for a simple reason: You 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 displays a message box if the field count is greater than 1.

If you need to remove this limitation, simply delete or comment out Step 4 in the macro.

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 repot filter that currently has focus.

For each pivot item, the macro captures the item name and uses it to change the report filter selection. This effectively alters the pivot table report to match the pivot item.

Step 7 prints the active sheet and then moves to the next pivot item. After you have 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 the macro

You can implement this kind of a macro in a standard module:

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

How the macro works

In the Excel object model, the Report Filter drop-down list is known as PageField. To print a pivot table 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 pivot table report to a new workbook.

Sub Macro1()

'Step 1: Declare your variables
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

'Step 2: Point to the pivot table in the active cell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3: Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside a pivot table."
    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

Step 1 declares three variables: pt is the memory container for your pivot table, pf is a memory container for your page fields, and pi holds each pivot item as the macro loops through the PageField object.

The active cell must be inside a pivot table for this macro to run. The assumption is that when the cursor is inside a particular pivot table, you will want to perform the macro action on that pivot.

Step 2 sets the pt variable to name of the pivot table 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 a pivot table, an error is thrown. You use the On Error Resume Next statement to tell Excel to continue with the macro if there is an error.

Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not in a pivot table, thus no pivot table could be assigned to the variable. If this is the case, the macro uses a message box to notify the user and then exits the procedure.

Step 4 determines whether there is more than one report filter field. If the count of PageFields is greater than 1, there is more than one report filter. You do this check 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 displays a message box if the field count is greater than 1.

To remove the one report filter limitation, delete or comment out Step 4 in the macro.

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 repot filter that currently has focus.

For each pivot item, Step 6 captures the item name and uses it to change the report filter selection. This step effectively alters the pivot table report to match the pivot item.

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 pivot table. You then paste to 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.

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 the macro

You can implement this kind of a macro in a standard module:

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

How the macro works

All charts belong to the ChartObjects collection. To take an action on all charts at one time, you simply iterate through all the charts in ChartObjects. Each chart in the ChartObjects collection has an index number that you can use to bring it into focus. For example, ChartObjects(1) points to the first chart in the sheet.

In this macro, you use this concept to loop through the charts on the active sheet with a simple counter. Each time a new chart is brought into focus, you change its height and width to the size you’ve defined.

Sub Macro1()

'Step 1: Declare your variables
     Dim i As Integer

'Step 2: Start looping through all the charts
     For i = 1 To ActiveSheet.ChartObjects.Count

'Step 3: Activate each chart and size
    With ActiveSheet.ChartObjects(i)
    .Width = 300
    .Height = 200
    End With

'Step 4: Increment to move to next chart
     Next i

End Sub

Step 1 declares an integer object, variable i, to be used as a looping mechanism.

Step 2 starts the looping by setting i to count from 1 to the maximum number of charts in the ChartObjects collection on the active sheet. When the code starts, i initiates with the number 1. As you loop, the variable increments by 1 sheet.

Step 3 passes i to the ChartObjects collection as the index number to bring a chart into focus. Then the width and height of the chart is set to the number you specify in the code. You can change these numbers to suit your needs.

In Step 4, the macro loops back around to increment i by 1 and get the next chart. After all charts have been evaluated, the macro ends.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate 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 in to the newly created blank module.

How the macro works

Every chart has four properties that dictate its size and position: Width, Height, Top, and Left. Interestingly enough, every Range object has these same properties. So if you set a chart's Width, Height, Top, and Left properties to match that of a particular range, the chart essentially snaps to that range.

After you decide how you want your dashboard to be laid out, note the ranges that encompass each area of your dashboard. Then use those ranges in this macro to snap each chart to the appropriate range. In this example, you adjust four charts to so that their Width, Height, Top, and Left properties match a given range.

Note that you're identifying each chart with a name. Charts are, by default, named Chart and the number in which they were added (Chart 1, Chart 2, Chart 3, and so on). You can see what each chart is named by clicking it, and then going up to the Ribbon and choosing Format   ⇒  Selection Pane. This command activates a task pane listing all the objects on your sheet, as shown in Figure 8-5.

You can use the Selection task pane to get the appropriate chart names for your version of this macro.

Sub Macro1()

Dim SnapRange As Range

Set SnapRange = ActiveSheet.Range("B6:G19")
   With ActiveSheet.ChartObjects("Chart 1")
    .Height = SnapRange.Height
    .Width = SnapRange.Width
    .Top = SnapRange.Top
    .Left = SnapRange.Left
   End With

Set SnapRange = ActiveSheet.Range("B21:G34")
   With ActiveSheet.ChartObjects("Chart 2")
    .Height = SnapRange.Height
    .Width = SnapRange.Width
    .Top = SnapRange.Top
    .Left = SnapRange.Left
   End With

Set SnapRange = ActiveSheet.Range("I6:Q19")
   With ActiveSheet.ChartObjects("Chart 3")
    .Height = SnapRange.Height
    .Width = SnapRange.Width
    .Top = SnapRange.Top
    .Left = SnapRange.Left
   End With

Set SnapRange = ActiveSheet.Range("I21:Q34")
   With ActiveSheet.ChartObjects("Chart 4")
    .Height = SnapRange.Height
    .Width = SnapRange.Width
    .Top = SnapRange.Top
    .Left = SnapRange.Left
   End With

End Sub

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in to the newly created module.

How the macro works

This macro uses the ShapeRange.Group method to group all the charts on the active sheet into one shape. This action is similar to what you would do if you were to group a set of shapes manually. After the charts are grouped, you copy the group and paste it to a new workbook. You then use the BreakLink method to remove references to the original source data. The BreakLink method ensures that Excel hard-codes the chart data into array formulas.

Sub Macro1()

'Step 1: Declare your variables
Dim wbLinks As Variant

'Step 2: Group the charts, copy the group, and then ungroup
    With ActiveSheet.ChartObjects.ShapeRange.Group
    .Copy
    .Ungroup
    End With

'Step 3: Paste into a new workbook and ungroup
    Workbooks.Add.Sheets(1).Paste
    Selection.ShapeRange.Ungroup

'Step 4: Break the links
    wbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    ActiveWorkbook.BreakLink Name:=wbLinks(1), _
                             Type:=xlLinkTypeExcelLinks

End Sub

Step 1 declares the wbLinks Variant variable. The macro uses this variable in Step 4 to pass the link source when breaking the links.

Step 2 uses ChartObjects.ShapeRange.Group to group all the charts into a single shape. The macro then copies the group to the clipboard. After the group is copied, the macro ungroups the charts.

Step 3 creates a new workbook and pastes the copied group to Sheet 1. After the group has been pasted, you can ungroup so that each chart is separate again. Note that the newly created workbook is now the active object, so all references to ActiveWorkbook point back to this workbook.

Step 4 captures the link source in the wbLinks variable. The macro then tells Excel to break the links.

This technique converts the chart source links to an array formula, so it can fail if your chart contains too many data points. How many is too many? The number can be different depending on the memory limits of the PC you’re working with.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code into the newly created module.

How the macro works

All charts belong to the ChartObjects collection. To take an action on all charts at one time, you simply iterate through all the charts in ChartObjects. Each chart in the ChartObjects collection has an index number that you can use to bring it into focus. For example, ChartObjects(1) points to the first chart in the sheet.

In this macro, you use this concept to loop through the charts on the active sheet with a simple counter. Each time a new chart is brought into focus, you print it.

Sub Macro1()

'Step 1: Declare your variables
     Dim ChartList As Integer
     Dim i As Integer

'Step 2: Start looping through all the charts
     For i = 1 To ActiveSheet.ChartObjects.Count

'Step 3: Activate each chart and print
         ActiveSheet.ChartObjects(i).Activate
         ActiveChart.PageSetup.Orientation = xlLandscape
         ActiveChart.PrintOut Copies:=1

'Step 4: Increment to move to next chart
     Next i

End Sub

Step 1 declares an integer object, variable i, as a looping mechanism.

Step 2 starts the looping by setting i to count from 1 to the maximum number of charts in the ChartObjects collection on the active sheet. When the code starts, i initiates with the number 1. As you loop, the variable increments by 1 until it reaches a number equal to the maximum number of charts on the sheet.

Step 3 passes i to the ChartObjects collection as the index number. This brings a chart into focus. You then use the ActiveChart.Printout method to trigger the print. Note that you can adjust the Orientation property to either xlLandscape or xlPortrait, depending on what you need.

Step 4 loops back around to increment i by one and get the next chart. After all charts have been printed, the macro ends.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate 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 into the newly created module.