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 टिप्पणियां
Please do not enter any spam link in the comment box.