In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using a drop down list. The tutorial workbook contains three different tables (Table1, Table2 and Table3) with identical column headers.

In this tutorial:

  • Create a combo box (form control)
  • Select input range
  • Add vba code to your workbook
  • Assign a macro to drop down list

Create a combo box (form control)

  1. Go to the developer tab
  2. Click "Insert" button
  3. Click combo box (form control)
  4. Create a combo box

Select input range

  1. Right click on combo box
  2. Click "Format control..."
  3. Go to "Control" tab
  4. Select an input range (D2:D4)
  5. Click OK

Cell range D2:D4 contains the table names, you probably need to change these names.

Add vba code to your workbook

  1. Press Alt+F11
  2. Select your workbook in the project explorer window
  3. Insert a new module to your workbook
  4. Add code below to code window
  5. Return to excel
Sub ChangeDataSource()
With ActiveSheet.Shapes(Application.Caller).ControlFormat
    ActiveSheet.PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    .List(.Value)
End With
End Sub

For this code to work you need to change pivot table name (bolded) in vba code: PivotTables("PivotTable1") to the name of your specific pivot table.

Assign a macro

  1. Right click combo box
  2. Select "Assign macro..."
  3. Select ChangeDataSource macro
  4. Click OK!

Download workbook

Pivot table - change data source range vba.xlsm