Chapter 8

Automating Common Reporting Tasks

In This Chapter

arrow Refreshing pivot tables with macros

arrow Leveraging macros to format pivot tables

arrow Applying pivot table restrictions

arrow Printing and exporting pivot data

arrow Resizing and aligning charts

arrow Exporting a set of disconnected charts

arrow Printing all charts on a worksheet

For those of us tasked with building dashboards and reports, PivotTables and charts are a daily part of our work life. Few of us have had the inclination to automate any aspect of these reporting tools with macros. But some aspects of our work lend themselves to a bit of automation. In this chapter, you explore a handful of macros that can help you save time and gain efficiencies when working with pivot tables and charts.

Refreshing All Pivot Tables in a Workbook

It’s not uncommon to have multiple pivot tables in the same workbook. Many times, these pivot tables link to data that changes, requiring a refresh of the pivot tables. If you find that you need to refresh your pivot tables en masse, you can use the macro in this section.

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.

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

Creating a Pivot Table Inventory Summary

When your workbook contains multiple pivot tables, it’s often helpful to have an inventory summary that outlines basic details about the pivot tables (similar to the one in Figure 8-1).

image

Figure 8-1: A pivot table inventory summary.

With this type of summary, you can quickly see important information such as the location of each pivot table, the location of each pivot table’s source data, and the pivot cache index that each pivot table is using.

The macro in this section outputs such a summary.

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.

Adjusting All Pivot Data Field Titles

When you create a pivot table, Excel tries to help you out by prefacing each data field header with Sum of, Count of, or whichever operation you use. Often, this behavior is not conducive to your reporting needs. You want clean titles that match your data source as closely as possible. Although it’s true that you can manually adjust the titles for data fields (one at a time), the following macro fixes them all in one go.

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.

Setting All Data Items to Sum

When creating a pivot table, Excel defaults to summarizing your data by either counting or summing the items. The logic Excel uses to decide whether to sum or count the fields you add to your pivot table is simple. If all cells in a column contain numeric data, Excel chooses Sum. If the field you are adding contains a blank or text, Excel chooses Count.

Although this logic seems to make sense, in many instances a pivot field that should be summed legitimately contains blanks. In these cases, you are forced to manually go in after Excel and change the calculation type from Count to Sum. That’s if you’re paying attention! It’s not uncommon to miss the fact that a pivot field is being counted instead of summed.

The macro in this section aims to help by automatically setting each data item’s calculation type to Sum.

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.

image

Figure 8-2: Excel displays your enumeration choices.

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.

Applying Number Formatting to All Data Items

A pivot table does not inherently store number formatting in its pivot cache because formatting takes up memory. To be as lean as possible, the pivot cache contains only data. Unfortunately, as a result, you need to apply number formatting to every field you add to a pivot table. This process takes from 8 to 10 mouse clicks for every data field you add. When you have pivot tables that contain five or more data fields, you’re talking about more than 40 mouse clicks!

Ideally, a pivot table should be able to look back at its source data and adopt the number formatting from the fields there. The macro outlined in this section is designed to do just that. It recognizes the number formatting in the pivot table’s source data and applies the appropriate formatting to each field automatically.

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.

Sorting All Fields in Alphabetical Order

If you frequently add data to your pivot tables, you may notice that new data doesn't automatically fall into the sort order of the existing pivot data. Instead, it gets tacked to the bottom of the existing data. This means your drop-down lists display existing data sorted alphabetically but display new data at the bottom of the list.

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.

Applying Custom Sort to Data Items

On occasion, you may need to apply a custom sort to the data items in your pivot table. For instance, if you work for a company in California, your organization may want the West region to come before the North and South. In these types of situations, neither the standard ascending nor the standard descending sort order will work.

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

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

Applying Pivot Table Restrictions

When you send pivot table reports to clients, coworkers, managers, and other groups of people, you might want to restrict the types of actions your users can take on the reports. The macro outlined in this section demonstrates some of the protection settings available through VBA.

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.

Applying Pivot Field Restrictions

Like pivot table restrictions, pivot field restrictions enable you to restrict the types of actions your users can take on the pivot fields in a pivot table. The macro outlined in this section demonstrates some of the protection settings available through VBA.

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.

Automatically Deleting Pivot Table Drill-Down Sheets

One of the coolest features of a pivot table is that it gives you the ability to double-click a number and drill into the details. The details are output to a new sheet that you can review. In most cases, you don’t want to keep these sheets. In fact, they often become a nuisance because you need to take time to delete them.

This behavior is especially a problem when you distribute pivot table reports to users who frequently drill into details. There is no guarantee that they will remember to clean up the drill-down sheets. Although these sheets probably won't cause issues, they can clutter up the workbook.

Implement the technique described in this section, and your workbook will automatically remove these drill-down sheets.

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

Figure 8-3: Enter your code in the Worksheet BeforeDoubleClick event.

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

Figure 8-4: Enter your code in the Worksheet BeforeDoubleClick event.

Printing a Pivot Table for Each Report Filter Item

Pivot tables provide an excellent mechanism to parse large data sets into printable files. You can build a pivot table report, complete with aggregations and analysis, and then place a field (such as Region) into the report filter. With the report filter, you can select each data item one at a time, and then print the pivot table report.

The macro in this section demonstrates how to automatically iterate through all the values in a report filter and print.

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.

Creating a Workbook for Each Report Filter Item

Pivot tables provide an excellent mechanism to parse large data sets into separate files. You can build a pivot table report, complete with aggregations and analysis, and then place a field (such as Region) into the report filter. With the report filter, you can select each data item one at a time, and then export the pivot table 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 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.

Resizing All Charts on a Worksheet

When building a dashboard, you often want to achieve symmetry and balance. In many cases, achieving symmetry in your dashboard requires chart size standardization. The macro in this section gives you an easy way to set a standard height and width for all your charts at once.

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.

Aligning a Chart to a Specific Range

Along with adjusting the size of charts, many of us spend a good bit of time positioning them so that they align nicely in our dashboards. This macro easily snaps your charts to defined ranges, with perfect positioning every time.

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.

image

Figure 8-5: Your chart objects and their names.

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.

Creating a Set of Disconnected Charts

When you need to copy charts from a workbook and paste them elsewhere (in another workbook, in PowerPoint, in Outlook, and so on), it’s often best to disconnect them from the original source data. In this way, you won’t get any annoying missing link messages that Excel throws. The macro in this section copies all the charts in the active sheet, pastes them into a new workbook, and disconnects them from the original source data.

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.

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

Printing All Charts on a Worksheet

To manually print a chart, you can click any embedded chart in your worksheet and then click Print. This action prints the chart on its own sheet without any other data on the sheet. Although manually printing a chart sounds easy enough, but it can become a chore if you have to do it for many charts. This macro makes short work of this task.

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.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.2.68