Categories
Excel

Automate PivotTable Creation

One way to create pivot tables automatically is to use the Recommended PivotTables feature. This feature scans your data and suggests some possible pivot tables that you can choose from. You can also use VBA to create pivot tables automatically. VBA gives you more control and flexibility over the pivot table creation process.

  1. Use Recommended PivotTables Feature to Create Pivot Tables Automatically.
  2. Use VBA to Automate Pivot Table Creation.

Automate PivotTable Creation using Recommended PivotTable

  1. Place your cursor in a tabular range of data
  2. Click the Insert tab of the ribbon.
  3. Click the Recommended PivotTables icon.

Excel shows you a menu of pivot tables it thinks it can create for you, based on the data in your range. When you find one that looks good to you, click it and then click OK to have Excel create it.

Use VBA to Automate PivotTable Creation

The steps you need to follow to create a PivotTable require some effort, and that effort often is redundant. With a small bit of VBA, you can create simple PivotTables automatically.

Two of the most commonly asked questions in Excel concern

  1. how to get a count of all items in a list, and
  2. how to create a list of unique items from a list that contains many duplicates.

In this section, we’ll show you how to create a PivotTable quickly and easily that accomplishes these tasks.

PivotTable: how to get a count of all unique items in a list

Assume you have a long list of names in column A, with cell A1 as your heading, and you want to know how many items are on the list, as well as generate a list of unique items.

  1. Select cell A1 or any cell within your data.
  2. Go to Insert tab of the ribbon and click PivotTable icon.
  3. The “Create PivotTable” dialog box should automatically pick up the correct range for your data in column A and will highlight it in your sheet.
  4. Choose where to place the PivotTable from the “Create PivotTable” dialog box.
  5. Click OK.
  6. In the PivotTable Fields pane on the right, drag and drop the field you want to count into the Values box. This field should be the column containing the items you want to count. By default, Excel will use the SUM function to aggregate the values, but you can change it to COUNT by configuring the Value Field Settings:
    • Click on the drop-down arrow next to the field in the Values area and select Value Field Settings.
    • Choose Count in the “Value Field Settings” dialog box.
    • Click OK to apply the changes.
  7. Again, to display a unique item list, drag and drop the field into the Rows box. This field should be the column containing the items you want to display as row headings.

The PivotTable will now display the unique items from your list along with a count of how many times each item appears in your list.

What if you want to have a macro perform all those steps for you, creating a PivotTable from any column you feed it?

  1. Press Alt+F11 or (Command+F11) to open the Visual Basic Editor (VBE).
  2. In the VBE window, select Insert » Module.
  3. Enter the following code:
Sub GetCount( )
Dim Pt As PivotTable
Dim strField As String
    strField = Selection.Cells(1,1).Text
    Range(Selection, Selection.End(xlDown)).Name = "Items"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:="=Items").CreatePivotTable TableDestination:="", _
    TableName:="ItemList"
    Set Pt = ActiveSheet.PivotTables("ItemList")
     ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)
     Pt.AddFields RowFields:=strField
     Pt.PivotFields(strField).Orientation = xlDataField
End Sub
  1. Close the VBE window and save your workbook.
  2. Before running this code, select the heading of your list and ensure that your list contains no blank cells.
  3. The code will automatically create a named range of your list, called Items. It will then create the PivotTable based on this named range on a new worksheet.

The next time you have a long list of data, you can simply select its heading and run this macro. All the PivotTable setup work will be done in the blink of an eye.

Pivot Tables

  1. Explaining PivotTables
  2. Create a PivotTable
  3. Automate PivotTable Creation
  4. Save or Share Pivot Table Without Source Data
  5. Extract PivotTable Data Using GETPIVOTDATA