VBA: Capture "Parent" Row Data by Target Selection

[sixfourtykilo]

Hi. Long time lurker, first time poster. I haven't had a need to join any of the Excel forums as Google has been my friend. There's lots of great information out there and even if I can't find my specific solution, I usually can find enough details to drive me in the right direction. However! I haven't found a specific solution to my problem, which makes me think I'm making this way harder than it needs to be. I have the following requirements:

1. User clicks a (any) value in a Compact Pivot Table
2. VBA captures that value, plus the "Parent Values"

In this example, user clicks "Go Live - Application Support". I need to capture "Oakley" and "Business Applications":

When you hover any item in the table, you get exactly what I'm trying to capture, but how do I get that data?

I'm not sure if this is just a difficulty of the table being in a Compact layout, but i'm trying to build a query based on the data that the user is selecting from the table. Cascading drop-down lists didn't seem to fit my requirements as the Pivot Table basically has the layout and filtering mechanisms that would make it more user-friendly.

 

[sixfourtykilo]

So this "works" in tabular view. It's dirty, but gets my point across. I still need this to work in Compact. Still working it.

For Each i In Selection.Rows
 Debug.Print i.PivotItem.Parent.Value & " = " & i.PivotItem.Value 
 Debug.Print i.Offset(0, -1).PivotItem.Parent.Value & " = " & i.Offset(0, -1).PivotItem.Value
 Debug.Print i.Offset(0, -2).PivotItem.Parent.Value & " = " & i.Offset(0, -2).PivotItem.Value
Next

 

[Jerry Sullivan]

Hi sixfourtykilo,

If your PivotTable had DataFields, this could be done relatively easily and robustly using the .PivotCell object...

Sub GetRowFieldsStemIfDataFields()
 Dim lNdx As Long
 Dim rCell As Range, rDataCell As Range
 Dim sReturn As String
 
 Set rCell = ActiveCell
 If bIsPivotCell(rCell) = False Then
   MsgBox "This cell is does not have a PivotTable"
   Exit Sub
 End If

 If rCell.PivotTable.DataFields.Count = 0 Then
   MsgBox "This PivotTable does not have data fields."
   Exit Sub
 End If
   
 Set rDataCell = Intersect(rCell.EntireRow, _
   rCell.PivotTable.DataBodyRange.Resize(, 1))
    
 With rDataCell.PivotCell
   For lNdx = 1 To .RowItems.Count
      sReturn = sReturn & vbCr & _
         .Parent.RowFields(lNdx).Name & " = " & .RowItems(lNdx)
   Next lNdx
 End With
 Debug.Print Mid(sReturn, 2)
End Sub

Your OP example doesn't have any datafields. For that scenario, I think you would need to do a more complicated process of stepping through the cells.
This code should work for PivotTables that are either Compact, Outline or Tabular form. It won't work for PivotTables that mix those forms (not commonly used).

Sub GetRowFieldsStem()
 '--returns field-item pairs for activecell in pivottable and
 '    its parent field-items
 
 Dim lNdx As Long, lRow As Long, lCol As Long, lPosition As Long
 Dim rCell As Range, rLabels As Range
 Dim sReturn As String, sFieldCurr As String
 Dim vFields As Variant
 
 Set rCell = ActiveCell
 If bIsPivotCell(rCell) = False Then
   MsgBox "This cell is does not have a PivotTable"
   Exit Sub
 End If
 
 '---make array of rowfields by position to trace each row in hierarchy
 With rCell.PivotTable
   ReDim vFields(1 To .RowFields.Count)
   For lNdx = 1 To .RowFields.Count
      vFields(.RowFields(lNdx).Position) = .RowFields(lNdx).Name
   Next lNdx
 End With

 With rCell.PivotTable.RowRange
   If .PivotTable.RowFields(1).LayoutCompactRow Then
      '--Compact layout
      lNdx = UBound(vFields) + 1
      Set rLabels = .Offset(1).Resize(rCell.Row - .Row)
      '--step up each row item capturing data when pivotfield changes
      For lRow = rLabels.Rows.Count To 1 Step -1
         sFieldCurr = rLabels(lRow).PivotField.Name
         lPosition = Application.Match(sFieldCurr, vFields, 0)
         If lPosition < lNdx Then
            sReturn = vbCr & rLabels(lRow).PivotField.Name & " = " _
               & rLabels(lRow).PivotItem.Name & sReturn
            lNdx = lPosition
            If lNdx = 1 Then Exit For
         End If
      Next lRow
   Else    '--Tabular or Outline layout
      For lCol = .Column To rCell.Column
         With .Parent.Cells(rCell.Row, lCol)
            sReturn = sReturn & vbCr & _
               .PivotField.Name & " = " & .PivotItem.Name
         End With
      Next lCol
   End If
 End With
 Debug.Print Mid(sReturn, 2)
End Sub

 

[sixfourtykilo]

btw thanks for the help. i ended up using a slicer after all as i can control the layout/appearance. however i did tuck this tidbit into the bottom of my pocket. i'm working on something else, with the same requirements, so i'm evaluating if this is still beneficial to my needs.

thanks again.

 

[kvarner]

Sorry to dredge up the past here, but this post has gotten me farther than the rest of the last 2 days of searching combined!!
Here's my original question so you know what's going on....."I have a pivot table that is made from a summary of 3 different tables so the drill down doesn't work on it. I want to use the BeforeDoubleClick and just write it myself. I know all the values I need are there because if I hover over a cell I can see them all, just don't know how to access them.

BeforeDoubleClick it will need to look at 3 row sources: Department, Review Account, Account - CP...I need the values of each one. Then I need the Column also.

So IF the column is "Budgeted" it will run and Index or Match on the "Budgeted" Worksheet and return all records where Department, Review Account and Account - CP all match. If the column is "Actual" it will do the same but on the Expense worksheet. And if the column is Committed on the Commitment worksheet.

Seems like a pretty basic If statement with a lookup, I think I'm just not familiar enough with what everything is called in a PivotTable. It's in tabular layout already as I suspected that would make things simpler."

Using the above code, in the immediate window, I can see what Department, ReviewAcct, and Account - CP are all equal to for the cell that I had active. Which is great. But can those titles just be used as variables to search for?
And it says that Budget = Budget, which it should, but for other columns it could say Actual = Actual or Committed = Committed. And which one of those it is would determine which worksheet to search for the rowfields in.

Really appreciate any help. Still trying to figure out if I should have it just highlight the appropriate rows (as they're already sorted), or put them in a new sheet and activate it then delete when the file closes. But one step at a time!

 

[kvarner]

Oh, and Budgeted just happens to be the first column. However if I activate a cell in the 2nd column, Actual, It returns all the rowfields correctly, then it says Budgeted = Budgeted, then Actual = Actual.
That's going to cause a problem with telling it which worksheet to search.