VBA Index/Match with dynamic ranges

[leatherhen99]

Good Friday All,

I've learned so much for you all, but maybe I don't know how to search for my question to get the answer... Here's what I'm doing... creating a pivottable with data... then I'm creating a table on another sheet to index/match only the names that I need. Here's the formula for the 1st agent and 1st category:

=IFERROR(INDEX(Sheet1!$B$5:$CA$180,MATCH($B3,Sheet1!$A$5:$A$180,0),MATCH(C$2,Sheet1!$B$4:$CA$4,0)),"")

Dim wb4 As Workbook        
Dim PSheet As Worksheet
        Dim DSheet As Worksheet
        Dim PCache As PivotCache
        Dim PTable As PivotTable
        Dim PRange As Range
        Dim LastRow As Long
        Dim LastCol As Long
        Dim ARange As Range
        Dim ColRange As Range
        Dim i As Long
        Dim c As Long

Set wb4 = ActiveWorkbook
'Pivottable is on PSheet

'get index array
        LastRow = PSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = PSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set PRange = PSheet.Cells(4, 3).Resize(LastRow, LastCol)
        

'paste headers from pivottable into table matrix
        Range("C3").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Flex").Select
        Range("B2").PasteSpecial
        Range("B3").Select
'get ARange to match array agents
'get ColRange to match array column headers 
        i = PSheet.Cells(Rows.Count, 1).End(xlUp).Row
        c = PSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set ARange = PSheet.Cells(4, 3).Resize(i, 0)
        Set ColRange = PSheet.Cells(3, 3).Resize(0, c)

I cannot get the formula to work utilizing the ranges (which may not be correct either...). I have tried to use the Immediate window, but I'm not well versed in using that to determine ranges...so my struggle is real! I have deleted all my previous attempts at the formula, so I don't even have that as an example... sorry!

Any help would be greatly appreciated!