Pivot

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