link multiple pivot tables to same data

[drluggo]

I have a VBA progema that imports a bunch of data to a worksheet and I have several other worksheets in the same workbook that contain pivot tables. In my macros I import the text file to the source data work sheet and then I go through all of the pivot tables and set the data source property to the range of data on the source data worksheet (I set the range dynamically since the amount of data is different each time).

This works fine for small amounts of data, but when the data source is large I have problems. When I leave screen updating on, I can see that it goes through the first three pivot tables fine, but when it gets to the fourth, it just hangs and I get an "out of memory" error. I assume that this is because I am caching the data over and over. I know that there is a way to link the pivot tables so I only have to set the source data once and all tables will look at this cache, but I don't know how to do it in VBA.

Can anyone help?

Here is the code that sets the source data:

Sub RefreshCharts()

Dim strRange As String
Dim strLRange As String
Dim strRowNum As String

Dim wksSheet As Worksheet
Dim chrtChart As Chart
Dim rngRange As Range

Sheets("Imported_Events&quot.Select
Range("A1&quot.End(xlDown).Select
strRowNum = ActiveCell.Row

strRange = "Imported_Events!R1C1:R" & strRowNum & "C12"
strLRange = "L" & strRowNum

Set rngRange = ActiveWorkbook.Worksheets("Imported_Events&quot.Range("A1", strLRange)

For Each wksSheet In ActiveWorkbook.Worksheets
If (wksSheet.PivotTables.Count > 0) Then
wksSheet.Select
wksSheet.PivotTables(1).SourceData = strRange
'$$$$ wksSheet.PivotTables(1).RefreshTable
End If
Next

'$$$$ ActiveWorkbook.RefreshAll

Set wksSheet = Nothing
Set chrtChart = Nothing
Set rngRange = Nothing

End Sub

 

[SkipVought]

Hi,

Ususlly, when you are building your PivotTables, the PivotTable Wizard will ask if you want to base the subsequent query on a previous cache. You might want to redo them.

But first, try this slimmed down routine...

for each ws in worksheets
  for each pt in ws.pivottables
    pt.PivotCache.Refresh
  next
next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com