Is the active cell in a pivot table row field

[Ziggy12]

Hi there
How do I tell if the active cell is in a row field and which one if there is more than one.

Is there a way to returnt he name of the row field the active cell is in?

cheers
ziggy

 

[Trevor G]

Look at using a Function Called Address that might help you.

 

[Ziggy12]

That'd be a row field in a pivot table.

 

[Ziggy12]

At least I can see if I'm in the right field with

ActiveCell.PivotField.Name.

 

[Jerry Sullivan]

This thread is somewhat dated, but here is a function that might
still be of use to you, or helpful to someone else who finds this thread.

Function isPTRowRange(c As Range) As Boolean
    Dim varRet As Variant
    On Error Resume Next
    varRet = Intersect(c, c.PivotCell.Parent.RowRange)
    isPTRowRange = Err.Number = 0
End Function

Call the function with ActiveCell or any Cell Reference...

Sub TEST()
    MsgBox isPTRowRange(ActiveCell)
End Sub

 

[Ziggy12]

Thanks for that
I solved it with this. If the active cell is in a row feild it returns the feild name but also return the feild name if the name itself is the active cell so turned of any action with the bold bit below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrorHandler

Dim SepMonth As String, CareType As String, ServiceArea As String, ClinUnit As String
Dim UnitPT As PivotTable, WardPT As PivotTable, PT As PivotTable

Set UnitPT = Worksheets("LoS Drill Down").Range("A4").PivotTable
Set WardPT = Worksheets("LoS Drill Down").Range("M4").PivotTable
Set PT = ActiveCell.PivotTable

If Application.Range(ActiveCell.Address).PivotCell.PivotCellType <> xlPivotCellValue Then
If PT.Name = "UnitPT" And ActiveCell.PivotField.Name = "Clin Unit" And ActiveCell.Value <> "Clin Unit" Then
WardPT.PivotFields("Service Area").CurrentPage = UnitPT.PivotFields("Service Area").CurrentPage.Name
WardPT.PivotFields("Care Type").CurrentPage = UnitPT.PivotFields("Care TYpe").CurrentPage.Name
WardPT.PivotFields("Sep Month").CurrentPage = UnitPT.PivotFields("Sep Month").CurrentPage.Name
WardPT.PivotFields("Clin Unit").CurrentPage = ActiveCell.Value
Application.Goto Reference:=Range("L1"), Scroll:=True
End If
End If
End
ErrorHandler: If PT Is Nothing Then End
End Sub

 

[Ziggy12]

To return the name of the rowfield
MsgBox Application.Range(ActiveCell.Address).PivotCell.RowItems.Item(1)

 

[Jerry Sullivan]

Hi again Ziggy,

I don't think that's correct. That code will return the associated PivotItem for the first RowField in the PivotTable, but not the RowField name itself.

That expression (if returning a pivot item is the desired result), could be simplified to....

   MsgBox ActiveCell.PivotCell.RowItems.Item(1)

Note that if you have multiple row fields, this will just return the row item associated with the first row field, not necessarily the row field on the same row as the active cell in a Pivot with Compact Form layout.