Change source data for pivotcache in vba?

[Asator]

I've got some code that consolodates my pivot tables into one pivot cache, but I can't get my code to change the source data for that pivot cache to my range. How do I go about doing that?

Public Sub pt_refresh()
Dim pt As PivotTable
Dim ws As Worksheet
Dim pf As PivotFields
Dim pc As PivotCache
Dim p_rng As Range
Dim ws_recon As Worksheet
Set ws_recon = ActiveWorkbook.Sheets("retro price recon")
With ws_recon
    Set p_rng = .Range(.Cells(10, 2), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count))
End With
For Each pc In ActiveWorkbook
    pc.SourceData = p_rng
Next pc
For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.CacheIndex = 1
        pt.RefreshTable
    Next pt
Next ws

It dies on "for each pc in activeworkbook", but I tried activeworkbook.pivotcaches(1).sourcedata = p_rng and that failed too. Ideas?

 

[Asator]

This forum scrolls almost as fast as /b/.