Change Pivot Table Source Data to Excel Table

Question

I am tring to write a simple macro to change the source data to a table in a neighboring worksheet and I am getting the following error:

Run-time error '438': Object doesn't support this property or method

The table is named "OEE 2013" and located in Worksheet "2013 OEE Data"

The pivot table is named "PivotTable1" and located in Worksheet "2013 OEE Pivot"

The pivot table currently references data from an external workbook and I would like to keep the same format and reference the table "OEE 2013" It may be easier to create a new pivot table but changes the source data seems to be a quicker solution to me. This will be repeated on a regular basis as part of a larger macro.

Sub change_pivot_source()

    Worksheets("2013 OEE Pivot").PivotTables("PivotTable1").ChangePivotCaches _
      ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
      SourceData:="OEE_2013", Version:=xlPivotTableVersion14)


End Sub

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-09-16T20:58:52

The error message is absolutely valid :)

Change .ChangePivotCaches to .ChangePivotCache

You might want to see the MSDN Link

Change Pivot Table Source Data to Excel Table

1 Answers