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