PivotSelect Question

[Paul_Hossler]

It seems that pivot tables use structured referencing like Tables do

The recorder generated this and it works to select a specific subtotal row, or all subtotal rows.

I can figure out most of it, but I can't find any explanation of the ";Sum" part in the information about structured references; what is and how to use it, other than it apparently finds the rows with 'sum' in the VENDOR field

Is that how it works?





Worksheets("Test1").PivotTables("TestPT1").PivotSelect "VENDOR[ACME PRODUCTS INC;sum]", xlDataAndLabel, True

Worksheets("Test1").PivotTables("TestPT1").PivotSelect "VENDOR[all;sum]", xlDataAndLabel, True

[Bob Phillips]

I don't know what your data is like or how you did that Paul, I get nothing like it.

[Paul_Hossler]

Using Win10, Pro with Office 365

I turned on Recorder

Hovered mouse over A11 (ACME Sum)

Move mouse to left until it turns into a Right Pointing Arrow (about the 'A')

Click

All VENDOR Sum (Subtotal) lines are selected in the pivot table

Stop Recorder



Sub Macro1()
ActiveSheet.PivotTables("PivotTable1").PivotSelect "VENDOR[All;Sum]", xlDataAndLabel, True
Selection.Interior.ColorIndex = 15
End Sub


I would have uploaded a WB, but I just wasn't understanding the syntax of the structured reference, but here's a cleaned up, small example

Thanks

[Bob Phillips]

Can't say I have come across it before, but I thought maybe it was saying all rows from columns VENDOR to the sum column.

I added subtotals back to Part, which labelled the subtotals PART03 Total, etc., and selecting that gave


ActiveSheet.PivotTables("PivotTable1").PivotSelect "PART[All;Total]", _
xlDataAndLabel, True

All very odd.

[Paul_Hossler]

All very odd.

1. That's Excel

2. It has something (I think) to do with the way structured references work when you use them in a ListObject table

3. The searching for structured references does not find anything about the ";Sum" type notation