PDA

View Full Version : Solved: Updating Source Range of Pivot Tables



Adaytay
06-15-2004, 02:59 AM
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.


'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

mark007
06-15-2004, 05:41 AM
This should do it:


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


:)

Adaytay
06-15-2004, 05:43 AM
Simply stunning, thanks Mark.

Ad

smozgur
06-15-2004, 05:56 AM
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.


'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

Ken Wright
06-19-2004, 01:41 PM
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..................