VBA Code to loop through Filter in pivot table

[Seshin Reddy]

I am new to VBA and trying to write a code I just cant seem to get right.

What I am trying to achieve:

I have a pivot table that has one row and column field. There is one filter that has more than 50 options. This is in cell c2 in the image. There is also a simple calculation that is being performed below which depends on the values in the pivot table. This is in cells c47:j47 I would like to create a code that runs through all the options in the filter and copies the results of the simple calculation into a new spreadsheet (Sheet5) along with the filter option heading.
So in a sheet5, cell c2 from Pivot (2) gets copied into cell A1 and c47:j47 from Pivot (2) gets copied to B1:H1 in Sheet5 for the first option in the filter and then it goes to the second option in the filter and pastes the results below.
Will anyone be able to help me with this? This is my code below. Where i get the error unable to get the PivotFields property of the PivotTable class.

Sub PivotStockItems()
    Dim i As Integer
    Dim sItem As String
    Dim pivotSht As Worksheet, dataSht As Worksheet

    Set pivotSht = Sheets("Pivot (2)") 'adjust to the name of sheet containing your pivot table
    Set dataSht = Sheets("Sheet5") 'as per your image

    Application.ScreenUpdating = False
    With pivotSht.PivotTables("CummulativeClaims")
        .PivotCache.MissingItemsLimit = xlMissingItemsNone
        .PivotCache.Refresh
        With .PivotFields("Yes")
            '---hide all items except item 1
            .PivotItems(1).Visible = True
            For i = 2 To .PivotItems.Count
                .PivotItems(i).Visible = False
            Next
            For i = 1 To .PivotItems.Count
                .PivotItems(i).Visible = True
                If i <> 1 Then .PivotItems(i - 1).Visible = False
                sItem = .PivotItems(i)

                'this takes care of the condition and copy-pasting
                If pivotSht.Range("c60").Value > 0 Then
                    dataSht.Cells(getLastFilledRow(dataSht) + 1, 1).Value = sItem
                    dataSht.Cells(getLastFilledRow(dataSht), 2).Value = pivotSht.Range("c60:j60").Value
                Else: End If

            Next i
        End With
    End With
End Sub

'gets last filled row number of the given worksheet
Public Function getLastFilledRow(sh As Worksheet) As Integer
    On Error Resume Next
    getLastFilledRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            LookAt:=xlPart, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

 

[Monty]

Hello Seshin.

Can I request you to provide excel attachment.

 

[Seshin Reddy]

Hi Monty. The file is too large to upload. I've realised that the picture i uploaded did not include the column and row headings.

 

[vletm]

Seshin Reddy
A sample file
or
would You do those Yourself ... manually ... for testing something?

 

[Seshin Reddy]

I've attached the sample file.