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 · Accepted Answer · 2013-09-16 20:58:52Z

2

The error message is absolutely valid :)

Change .ChangePivotCaches to .ChangePivotCache

You might want to see the MSDN Link

answered Sep 16, 2013 at 20:58

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rcm19 Over a year ago

Thank you so much @Siddharth Rout, I really appreciate it and thank you for the link.

Collectives™ on Stack Overflow

Change Pivot Table Source Data to Excel Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related