Changing The Range Of Dynamic Pivot Table: Change the range of pivot table dynamically Excel help response (03 Feb 2011)

Hi,

I believe the below code should allow users to update multiple pivots in multiple worksheets by using a dynamic range variable determined from using a Last function. The code below should also allow you to specify which datafields the user would like included.

My code is below:

VBA Code:

Sub Refresh_All_Pivots()
On Error GoTo err_handler
Application.ScreenUpdating = False
Dim field1 As String
Dim field2 As String
Dim field3 As String
Dim field4 As String
Dim field5 As String
Dim field6 As String
Dim field7 As String
Dim field8 As String
Dim ws As Worksheet
Dim Pt As PivotTable
Dim pf As PivotField
Dim rng As Range
Dim lastcol As Long
Dim LastRow As Long
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    For Each Pt In ws.PivotTables

    Set rng = Range("A4").currentregion

    LastRow = Last(1, rng)
    lastcol = Last(2, rng)
    field1 = rng.Cells(1, (lastcol) - 7).Text
    field2 = rng.Cells(1, (lastcol) - 6).Text
    field3 = rng.Cells(1, (lastcol) - 5).Text
    field4 = rng.Cells(1, (lastcol) - 4).Text
    field5 = rng.Cells(1, (lastcol) - 3).Text
    field6 = rng.Cells(1, (lastcol) - 2).Text
    field7 = rng.Cells(1, (lastcol) - 1).Text
    field8 = rng.Cells(1, (lastcol)).Text

    Pt.SourceData = Range("A4").currentregion.Address(True, True, xlR1C1, True)

        For Each pf In Pt.DataFields
            pf.Orientation = xlHidden
        Next pf

            With Pt.PivotFields(field1)
                .Orientation = xlDataField
                .Caption = "Sum of" & " " & field1
                .Function = xlSum
            End With


            With Pt.PivotFields(field2)
                .Orientation = xlDataField
                .Caption = "Sum of" & " " & field2
                .Function = xlSum
            End With

            With Pt.PivotFields(field3)
                .Orientation = xlDataField
                .Caption = "Sum of" & " " & field3
                .Function = xlSum
            End With

            With Pt.PivotFields(field4)
                .Orientation = xlDataField
                .Caption = "Sum of" & " " & field4
                .Function = xlSum
            End With

            With Pt.PivotFields(field5)
                .Orientation = xlDataField
                .Caption = "Sum of" & " " & field5
                .Function = xlSum
            End With

            With Pt.PivotFields(field6)
                .Orientation = xlDataField
                .Caption = "Sum of" & " " & field6
                .Function = xlSum
            End With

            With Pt.PivotFields(field7)
                .Orientation = xlDataField
                .Caption = "Sum of" & " " & field7
                .Function = xlSum
            End With
            With Pt.PivotFields(field8)
            .Orientation = xlDataField
            .Caption = "Sum of" & " " & field8
            .Function = xlSum
            End With

Pt.RefreshTable
Errorhandling:

    Next Pt
Next ws
ActiveWorkbook.ShowPivotTableFieldList = False
Application.ScreenUpdating = True
Sheet1.Activate
Exit Sub
err_handler:
MsgBox "One or more of the Pivot tables have experienced an error." & vbCrLf & vbCrLf & _
    "Please look through the workbook to determine where the error has occurred.", _
    vbOKOnly, "Error with Refreshing Pivots"
Resume Errorhandling
End Sub

Just completed this (I think) so thought I would share.

All ears if anyone encounters any problems?

Thanks

Carl

Changing the range of dynamic pivot table

Excel Help for Changing The Range Of Dynamic Pivot Table in Charting and Performing Technical Analysis

Changing The Range Of Dynamic Pivot Table