CODEWITHSUNDEEP
vbaexcel
AGryckiewicz
AGryckiewicz

Reputation: 119

VBA: Referencing a Pivot Table by name rather than index number

I have a simple question. What is the syntax for referencing a pivot table in VBA by its name rather than its index number?

For example, I could write this line like this:

With ThisWorkbook.Worksheets("Sheet1").PivotTables(1).PivotFields("InvestorNumber") End With

But I need to write it like this:

With ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("InvestorNumber") End With

Writing it the second way errors-out with "subscript not in range" although "PivotTable1" definitely exists on this worksheet and the pivot table's name is definitely "PivotTable1"

Any thoughts on what is wrong with the syntax?

MSDN says it can be referenced by name, but doesn't give an example of how you do it.

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivottables-object-excel

Thanks

Upvotes: 4

Views: 8414

Answers (1)

AGryckiewicz
AGryckiewicz

Reputation: 119

I found the answer to my problem. I was still using an index number to refer to the Worksheet and this was throwing the error. So this doesn't work:

With ThisWorkbook.Worksheets(1).PivotTables("PivotTable1").PivotFields("InvestorNumber") End With

While it appears this does work:

With ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("InvestorNumber") End With

If you want to use names rather than index numbers in the object reference, then names have to be used for all members; you can't use a mix of indexes and names apparently.

Upvotes: 1

Related Questions