Changing pivot table data source

Question

Nothing has made me more frustrated with VBA than pivot tables. I would like to simply specify a data source for a pivot table using VBA.

Dim pvtcache As PivotCache
Dim Range1 As Range

ActiveSheet.PivotTables("PivotTable4").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ThisWorkbook.Worksheets("Business Process Identification").Range("A1:Bc1693").Address(ReferenceStyle:=xlR1C1) _
, Version:=6)

I'm trying to open a workbook from a SP site, and then reference data for the data source, refresh, then close the workbook.

I get a runtime error '-2147024809' that says: "the PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. IF you are changing the name of a PivotTable field, you must type a new name for the field"

If I manually change the data source to the range specified in the code, there is no problems, only when I run this macro.

Note: The reference data for right now is in the same workbook, and I'm getting this same error. Note: I do not have hidden columns or rows, I have all my column headers labelled correctly, which is the usual cause of this problem

Issel do you want to change the sourcedata because it grows on your sheet, or it comes from a totally different source? — Damian
Once I figure out what the issue is, I plan on defining a range, and using that as the source data, it will change monthly, and there will be multiple Pivot Tables. I'm using a static reference for the moment to simplify and troubleshoot. Source data will come from a different workbook, saved on a SP site. — Issel
Using a ListObject as a data source, you never need to quadruple-check you're capturing all the rows ever again. — Mathieu Guindon
@Issel you can use a simple named range on your workbook referencing the other workbook like this: =OFFSET(SHEETNAME!A1,0,0,COUNTA(SHEETNAME!A:A),COUNTA(SHEETNAME!1:1)) this will give you a dynamic range growing with the sheet, thought you will need to open the other workbook to refresh it but... — Damian
Mathieu Guindon, if I could up-vote that post 42 times, I would do it. ListObjects (or Tables) are among the most under-utilized features of Excel. So many times I've seen people doing so much work to maintain ranges that could be done with almost no effort by using a ListObject. When you include the ability to extend formulas automatically throughout the whole of the table, the vastly simpler syntax you see when creating formulas and the ease of adding/deleting rows, ListObjects become one of the best data management tools in Excel. — Frank Ball

Issel Issel · Accepted Answer · 2019-06-11T16:19:48

after trying MANY different approaches and variations, I found that if you turn the range you want into a table, and use "ListObjects" changing the source data on a pivot table will work:

Workbooks("SubLOBs").Worksheets("APQCL2").PivotTables("PivotTable4").ChangePivotCache _
Workbooks("SubLOBs").PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ThisWorkbook.Worksheets("Business Process Identification").ListObjects("Table1"))

I have no idea why. I have no idea why a parameter called "SourceData" can't simply refer to a range, but has to be in a table and referred to as a listobject. Pivot Tables can suck a dick.

Changing pivot table data source

1 Answers