Find out what column pivot table heading is in

[excelio]

[FONT=&quot]Hi,[/FONT]
[FONT=&quot]I have a pivot table that has a number of values including revenue and volume and filters on a date range.[/FONT]
[FONT=&quot]The issue is if I change the date range the column called [/FONT]
[TABLE="width: 97"]
<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">[TR]
[TD="width: 97"]Total Revenue
[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]Also changes. I am requiring a way to find out what column the Total Revenue is in to then use in a count statement.[/FONT]
[FONT=&quot]Thanks[/FONT]

 

[pbornemeier]

Try this:

Option Explicit

Sub FindTermInPTColumnHeaders()
    
    'Assumption: Activesheet contains the pivot table
    'Assumption: The first PT on teh activesheet is the one to be examined
    'Assumption: the whitespace in your search term is a space
    'ActiveSheet.PivotTables(1).ColumnRange will limit find to the column header area\

    Const sFind As String = "Total Revenue"
    Dim oFound As Object
    Dim lColumn As Long
    
    Set oFound = ActiveSheet.PivotTables(1).ColumnRange.Find(What:=sFind, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not oFound Is Nothing Then
        lColumn = oFound.Column
    End If
    
    If lColumn = 0 Then
        MsgBox "'" & sFind & "' was not found in your PT Column Headers"
    Else
        MsgBox lColumn & " is the column containing '" & sFind & "' in the column headers."
    End If
End Sub