Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields

[ajjava]

Hello,
I've been slowly but surely writing/cobbling together code snippets, to gain some pivot table functionality (to be used for "quick and dirty" analysis of fairly large data sets).

I apologize in advance for when you see the associated code. I am on a deadline for how long I can work on this project, before I'm forced to go back to my "normal" daily tasks, so I'm CERTAIN I've not used best practices...but it works for now.

Visuals will be easier than words (also, I didn't realize I wouldn't be able to attach the actual Excel file), but I hope this clearly conveys what I'm trying to accomplish (but I do realize it's kind of a lot to absorb).
But, in a nutshell:

• ONE pivot table will be used, which will be populated, over and over, with a new data source
• That data source will ALWAYS be the same number of columns, with consistent column headings
• The pivot will ONLY ever show a max of THREE VALUE FIELDS (but could be only 1 or 2):
  • Count of unique TEXT items from a data source row record
  • Sum or Average of a CURRENCY column from a data source row record
    • VBA to toggle between showing SUM or AVERAGE has already been added
• The pivot will ONLY ever show TWO ROW FIELDS
  • Those fields can be swapped out by the user, but it will ALWAYS be only two

What I'm trying accomplish, with the pivot table described above, is depicted in the first image.
The key thing, I guess, is to eliminate ALL absolute references, to account for the changing shape of the pivot.
(The call-out box in the image says "return the MAX", but I also want that max value to be highlighted within the pivot table)

I've managed to create code that accomplishes these goals, but only when there is ONE VALUE field in the pivot table (second image).
It works as expected, which I'm really proud of myself for


Part Two of my quest is to return the name of the PivotItem that is associated with the identified MAX VALUE.
In the second image below, you can see that I've managed to accomplish this, as well...BUT it currently is an absolute reference, so if the number of VALUE FIELDS changes, the location of that PivotItem will change, and so will the output of the related procedure.

Here is an image of the results of the procedures I've written so far, when run on a pivot table with just ONE VALUE FIELD:

Related code:

Sub Get_Max_Values_PVT()
Dim PT As PivotTable
Dim PF As PivotField
Dim dfCOUNT As PivotField 'df stands for DATA FIELD, which is a property of PivotField. Used for VALUES in the PIVOT TABLE
Dim PI As PivotItem

Dim myRange As Range ' * get max related
Dim localMax As Long ' * get max related
Dim found As Range ' * get max related


Set PT = ActiveSheet.PivotTables(1) 'Sets pt to be the (x) PIVOT TABLE on the active sheet
Set PF = PT.PivotFields(11) 'Sets pf to be the (x) COLUMN (data field) in the DATA SOURCE (so, "Append1" is the data source in this case)
Set dfCOUNT = PT.DataFields(1) 'Sets df to be the (x) DATA FIELD (PivotField) in the PIVOT TABLE (1= Count of Formatted File Number)
Set PI = PF.PivotItems(4)



Set myRange = PT.PivotFields("Years").PivotItems("2023").DataRange


localMax = Application.WorksheetFunction.Max(myRange) ' * get max related
Range("i2").Select
Selection.Value = localMax 'Prints localMax associated in the myRange range, in cell I2


Set found = myRange.Find(localMax) 'Variable to identify the address of the function variable "localMax". Used below to get cell address of "localMax" ' *get max related
'Notice the location of the SET statement...placed AFTER assigning a value to localMax variable
PT.TableRange2.Interior.ColorIndex = 0 'Clears shading from prior localMax
found.Interior.ColorIndex = 22 'Sets the color of localMax to LIGHT RED
Range("j2").Select
Selection.Value = found.Offset(, -3).Value 'Prints PivotItem name associated with localMax in cell J2



End Sub