Referencing datarange of pivot table with multiple row fields

Question

Ok I got referencing the data ranges of a pivot table with a single pivot field in either the columns or rows:

BevPvt.PivotFields("Sum of Amount ").DataRange.Select
For Each deposit1 In BevPvt.PivotFields("Sum of Amount ").DataRange

etc.

but what if I have multiple data fields within columns or rows, IE moving two data fields to the row box (for instance POS location and Date). I want to find matching dates and matching locations. Is the only solution to move a row field (for example location) to a column?

Kurt VonOhlen Kurt VonOhlen · Accepted Answer · 2016-11-16T23:13:24

One way of working around this issue is by using offset(0,-1)

I turn off subtotals for the first datafield in the rowfield box, then have my macro searching the value field data range - if it finds vbNullString then the offset(0,-1) records the name of the data field in the first rowfield position, then if it is not a vbNullString then the offset gives the name of the data field in the second rowfield position.

Looks like this (the removal of subtotals is not in this portion of code)

For Each deposit2 In CHMBAll.PivotFields("Sum of Original Deposit Total").DataRange
If deposit2 = vbNullString Then
lockbox = deposit2.Offset(0, -1).Value
End If
If deposit2 <> vbNullString Then
If deposit2.Interior.Color = 5296274 Then
DepDate = deposit2.Offset(0, -1).Value
For CHMBi = 2 To CHMBLR
If Cells(CHMBi, 5).Value = lockbox Then
If Cells(CHMBi, 3).Value = DepDate Then
Cells(CHMBi, 6).Interior.Color = 5296274
End If
End If
Next CHMBi
End If
End If
Next deposit2

Referencing datarange of pivot table with multiple row fields

1 Answers