Updating Source Range of Pivot Tables

Hi folks... hopefully this is a quick question which can give a quick answer.

I have a spreadsheet that contains, among other things, a sheet that is updated via another process (gets a few hundred rows added). I have a pivot table that is based on this data, and I need the pivot table to detect the new records and refresh accordingly.

The following code snippet is what I've got in place currently - problem is though it does NOT update the range.

Code:

---

'Create a pivot table based on the data...
    Application.StatusBar = "Updating Pivot, please wait..."
    Sheets("Pivots").Select
    Sheets("Pivots").Activate
    With Sheets("Pivots")
    .PivotTableWizard SourceType:=xlDatabase, SourceData:="AllData!A1:E" & lngAllMaxRow + 2
    .PivotTables("CRTGDemand").Refresh
    End With
Application.CommandBars("PivotTable").Visible = False

---

The variable lngAllMaxRow contains the total number of data rows on the sheet AllData. The range A1:Exxx is the range I want the pivot table to reflect.

Any ideas? This is going to bug me, I can see...

Cheers,

Ad

This should do it:

Code:

---

Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim p As PivotTable
    Set p = ws.PivotTables("CRTGDemand")
    p.SourceData = "AllData!A1:E" & lngAllMaxRow + 2
    p.RefreshTable

---

:)

Simply stunning, thanks Mark.

Ad

Quote:

---

Originally Posted by Adaytay

Hi folks... hopefully this is a quick question which can give a quick answer.

I have a spreadsheet that contains, among other things, a sheet that is updated via another process (gets a few hundred rows added). I have a pivot table that is based on this data, and I need the pivot table to detect the new records and refresh accordingly.

The following code snippet is what I've got in place currently - problem is though it does NOT update the range.

Code:

---

'Create a pivot table based on the data...
    Application.StatusBar = "Updating Pivot, please wait..."
    Sheets("Pivots").Select
    Sheets("Pivots").Activate
    With Sheets("Pivots")
        .PivotTableWizard SourceType:=xlDatabase, SourceData:="AllData!A1:E" & lngAllMaxRow + 2
        .PivotTables("CRTGDemand").Refresh
    End With
Application.CommandBars("PivotTable").Visible = False

---

The variable lngAllMaxRow contains the total number of data rows on the sheet AllData. The range A1:Exxx is the range I want the pivot table to reflect.

Any ideas? This is going to bug me, I can see...

Cheers,

Ad

---

Hi,

Different approach: I would use a dynamic range as pivot table source so I wouldn't bother with changing its source but only refresh it either manually or via simple VBA code.

Please see the attachment.

Suat

Agree with Smozgur - Dynamic Source range has to be a better way than re-doing the source range by code:-

http://www.contextures.com/xlPivot01.html#Dynamic

Regards
Ken..................