Author: Oscar Cronquist Article last updated on February 24, 2019

In this article, 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

Want to learn more about Combo Boxes? Read this article:

Working with COMBO BOXES [Form Controls]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Working with COMBO BOXES [Form Controls]

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. Right-click on your workbook in the project explorer window.
  3. Click "Insert".
  4. Click "Module".
  5. Copy the code shown below and paste to code module, see image above.
  6. Return to Excel.
Sub ChangeDataSource()

With ActiveSheet.Shapes(Application.Caller).ControlFormat
    ActiveSheet.PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
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.

Note, remember to save our workbook with file extension *.xlsm (macro-enabled workbook) in order to keep the VBA code attached to the workbook.

Assign a macro

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