Pivot Table assistance : (How to use column headers in report filter in PT)

I have set of data where i have budget and actuals where on left side i have the description and on the column headers i have the months (its month wise data in column), I want to use the column headers as Report Filter while creating the Pivot Table, how can i do it?

I have attached the sample format of my data with requirement.

Kindly assist.

Instead of looking to Pivot it, why don't you use in B17 and drag across

=INDEX($A$3:$BE$5,MATCH($A17,$A$3:$A$5,0),MATCH($A $11,$A$2:$BE$2,0)+MATCH(B$16,$B$1:$BE$1,0)-1)

Thanks for the reply. Actually i m trying to create dash board using slicers in pivot table and this pivot table is linked to the set of selected slicers. I gave the sample data to know how to use dates in report filter.

Kindly assist on the above.

Guys, kindly help me on this, please.

Hi,

Run this script and create Pivot table.

Code:

---

Sub kTest()
   
    Dim ka, k(), i As Long, c As Long, n As Long
   
    ka = Range("a1:be5").Value    '<<<< adjust this
   
    ReDim k(1 To UBound(ka, 2) * UBound(ka, 1), 1 To 4)
   
    For i = 3 To UBound(ka, 1)
        For c = 2 To UBound(ka, 2)
            n = n + 1
            k(n, 1) = ka(i, 1)
            k(n, 2) = ka(1, c)
            k(n, 3) = ka(2, c)
            k(n, 4) = ka(i, c)
        Next
    Next
   
    With Sheets("Sheet2")
        .Range("a1:d1") = Array("Description", "Bud/Act", "Period", "Value")
        .Range("a2").Resize(n, 4) = k
    End With
   
End Sub

---

Thanks, if I add another row (A6) with data what changes i will make? I mean how to select wider range?

Hi,

Code:

---

ka = Range("a1:be5").Value    '<<<< adjust this

---

Thanks, but if i add columns after the "description" (lets say now 6 of them) column where do i change in script?

Hi,

See

Code:

---

Sub kTest()
   
    Dim ka, k(), i As Long, c As Long, n As Long
   
    ka = Range("a1:be6").Value    '<<<< adjust this
   
    ReDim k(1 To UBound(ka, 2) * UBound(ka, 1), 1 To 10) 'adjust the '10'
   
    For i = 3 To UBound(ka, 1)
        For c = 8 To UBound(ka, 2) 'start from 8th column
            n = n + 1
            k(n, 1) = ka(i, 1) 'desc
            k(n, 2) = ka(i, 2) 'test1
            k(n, 3) = ka(i, 3) 'test2
            k(n, 4) = ka(i, 4) 'test3
            k(n, 5) = ka(i, 5) 'test4
            k(n, 6) = ka(i, 6) 'test5
            k(n, 7) = ka(i, 7) 'test6
           
            k(n, 8) = ka(1, c) 'bud/act
            k(n, 9) = ka(2, c) 'period
            k(n, 10) = ka(i, c) 'value
        Next
    Next
   
    With Sheets("Sheet2")
        .Range("a1:j1") = Array("Description", "", "", "", "", "", "", "Bud/Act", "Period", "Value")
        .Range("a2").Resize(n, UBound(k, 2)) = k
    End With
   
End Sub

---