Solved change pivot table range and refresh

Mstest / Awrdacpi
January 27, 2013 at 18:42:57
Specs: Microsoft Windows XP Professional, 2 GHz / 958 MB
Hi,

I have 6 sheets with 2 pivot tables each (total of 12 pivot tables) in one workbook. These pivot tables referenced to only one data range which is in sheet DATA. I want to automatically refresh and change pivot table range as the range is changing starting from Row B10 up to the last row column.

I have a code here but it doesn't work.

Sub ChangeSource()

Dim sht As Worksheet
Dim lrow As Long
Dim lcol As Long
Dim pvt As PivotTable
Dim pvtsheet As String
 
For Each sht In Worksheets
     
     
    If sht.PivotTables.Count > 0 Then
        For Each pvt In sht
             'RETURN SPECIFIC SOURCE RANGE HERE
             'POSSIBLY MANIPULATE TEXT TO STOP AT "!" IF WHOLE RANGE IS RETURNED
             'SET PVTSHEET = TO SPECIFIC SHEET
            lrow = Sheets("DATA").Range("B" & Rows.Count).End(xlUp).Row
            lcol = Sheets("DATA").Range("A" & Columns.Count).End(xlUp).Column
            pvt.SourceData = Sheets("DATA").Range("B10:" & lcol & lrow).CurrentRegion.Address(True, True, xlR10C2, True)
        Next
    End If
Next

End Sub

Please do help in creating a code.

Thank you.


See More: change pivot table range and refresh

Report •

#1
January 27, 2013 at 23:00:38
✔ Best Answer
Hi

I couldn't figure out how to redefine the data table for a pivot in VBA so here's an alternate approach....

Define your data table as a "Table". This way any rows added will be included in the table definition

Redefine all your pivots using the table name as the range for the pivot.

In this way your pivots should always include all the data.

To refresh all pivots you can try this....

Sub ChangeSource()

Dim sht As Worksheet
Dim lrow As Long
Dim lcol As Long
Dim pvt As PivotTable
Dim pvtsheet As String

'lrow = Sheets("DATA").Range("B" & Rows.Count).End(xlUp).Row
'lcol = Sheets("DATA").Cells(10, Columns.Count).End(xlToLeft).Column

  For Each sht In Worksheets

nPivs = sht.PivotTables.Count
If nPivs > 0 Then
          For i = 1 To nPivs
                  sht.PivotTables(i).PivotCache.Refresh
        Next
    End If
Next

End Sub


Report •

#2
January 28, 2013 at 17:20:26
Hi Altek,

Thanks for the help.


Report •
Related Solutions


Ask Question