Capture PivotFields rows and column from PivotItem in VBA

[kvarner]

I've tons of examples on here of how to reference a PivotValue and even find the Row source, but not quite what I'm looking for.

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.

Thanks for any help!

 

[SpillerBD]

If you are also using PowerPivot/PowerQuery to get the 3 tables in one pivot, you might check the PowerPivot Forum
You might ask to get this moved to the PowerBI section instead...

(I know that's out there but I am able to keep my source data formatted where I don't need those solutions and am not well versed in PQ or PP... yet.)

 

[kvarner]

Unfortunately I don't have PowerPivot. My data is all in an AccessDb so that's where the query is, Excel just has a linked pivot table.

If I could just figure out how to extract the 3 RowFields and the ColumnField from the target I'd be set from there. But I can't figure out how to get them saved as separate variables that I can use to search with.

 

[SpillerBD]

Did you use: Data\Get External data\From Access
to connect to the database query and create a Table in Excel first?
Or is your Pivot Table looking at the Access Query directly?

 

[kvarner]

I used Get External Data to pull in the query. The query itself uses 3 different "Totals" queries off the 3 original tables, plus a Union qry that designates all the Departments and account types for each that way $0 amounts will show up for ones that have Budgeted amounts but no Actual expensed amounts, among a few other things. Microsoft Query is filtering the query on it's way to Excel as each department has to have their own file so they can't see the other department's info.

I found some code finally that returns the rowfields nicely, but it also returns all the columns going back to the left, not just the one the active cell is in.

 

[kvarner]

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