Pivot Table Relative References

[Jimmy]

Looking for help with VBA code.

I would like to store in a variable the amount of child categories that
are part of a given row or column.

Are pivot table size details referenced in any functions?

My user requires a formula inserted to the bottom of the pivot which
will summarize the above data. This requires knowing the cell
locations to put into these formulas.

Thank you,

JR

 

[Mark Driscol]

If the ActiveCell is in the pivot table, the code below can get the
addresses of the pivot table row and column the cell is in.

Option Explicit

Sub GetPivotTableInfo()

Dim rngDataRange As Range
Dim rngRow As Range
Dim rngColumn As Range

Set rngDataRange = Application.ActiveCell.PivotField.DataRange
Set rngRow = Intersect(Rows(ActiveCell.Row), rngDataRange)
Set rngColumn = Intersect(Columns(ActiveCell.Column), rngDataRange)

MsgBox "Row address: " & rngRow.Address
MsgBox "Column address: " & rngColumn.Address

End Sub


Mark

 

[Jimmy]

Thanks.
It works!

If the ActiveCell is in the pivot table, the code below can get the
addresses of the pivot table row and column the cell is in.

Option Explicit

Sub GetPivotTableInfo()

Dim rngDataRange As Range
Dim rngRow As Range
Dim rngColumn As Range

Set rngDataRange = Application.ActiveCell.PivotField.DataRange
Set rngRow = Intersect(Rows(ActiveCell.Row), rngDataRange)
Set rngColumn = Intersect(Columns(ActiveCell.Column), rngDataRange)

MsgBox "Row address: " & rngRow.Address
MsgBox "Column address: " & rngColumn.Address

End Sub


Mark