Pivot

Pivot VBA code

Refresh specific pivot table and add measures accordingly

Sub PivotRefresh()

Dim ws As Worksheet

Dim ws1 As Worksheet

Dim pt As PivotTable

Dim ptField(30) As PivotField

Dim Cur(30) As String

Dim i As Byte

Dim wb as Workbook


Set wb = ThisWorkbook

Set ws = wb.Sheets(PivotSht) 'change sheet name accordingly

Set ws1 = wb.Sheets(InputSht) 'change sheet name accordingly

Set pt = ws.PivotTables(1) 'change pivot number accordingly


'assign measures to array


For i = 1 To 30

    Cur(i) = ws1.Cells(2, i + 14)

Next i


With pt

    .PivotCache.Refresh

    For i = 21 To 30

        If i <= 24 Then

            Set ptField(i) = .AddDataField(.PivotFields(Cur(i) & "2"), Cur(i) & "2" & " ", xlSum)

            .InGridDropZones = True

            ptField(i).Position = i - 20

            .RowAxisLayout xlTabularRow

        Else

            Set ptField(i) = .AddDataField(.PivotFields(Cur(i) & "3"), Cur(i) & "3" & " ", xlSum)

            .InGridDropZones = True

            ptField(i).Position = i - 20

            .RowAxisLayout xlTabularRow

        End If

    Next i

End With


Set ws = Nothing

Set ws1 = Nothing

Set pt = Nothing


End Sub

टिप्पणी पोस्ट करें

0 टिप्पणियां