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 टिप्पणियां