Home > Forum Home > Charting and Performing Technical Analysis > Change the range of pivot table dynamically > Changing the range of dynamic pivot table Share

Changing the range of dynamic pivot table

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

Forum TopicLogin

Changing The Range Of Dynamic Pivot Table

Rate this:
(3/5 from 1 vote)

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
    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
    Next Pt
Next ws
ActiveWorkbook.ShowPivotTableFieldList = False
Application.ScreenUpdating = True
Exit Sub
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?



 Posted by on
View Full Post

Find relevant Excel templates and add-ins for Changing the range of dynamic pivot table in the