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. Press with left mouse button on "Insert" button
  3. Press with left mouse button on combo box (form control)
  4. Create a combo box

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

Recommended articles

test
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 […]

Select input range

  1. Press with right mouse button on on combo box
  2. Press with left mouse button on "Format control..."
  3. Go to "Control" tab
  4. Select an input range (D2:D4)
  5. Press with left mouse button on 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. Press with right mouse button on on your workbook in the project explorer window.
  3. Press with left mouse button on "Insert".
  4. Press with left mouse button on "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:= _
    .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.

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. Press with right mouse button on combo box
  2. Select "Assign macro..."
  3. Select ChangeDataSource macro
  4. Press with left mouse button on OK!

Get the Excel file


Pivot-table-change-data-source-range-vba.xlsm