I have a document that has multiple pivot tables. All pivots need to be
resourced to the same table within access. Is there a way to do this without
having to resource each table individually?

Thanks
Nick

Nick Anderson wrote:
I have a document that has multiple pivot tables. All pivots need to be
resourced to the same table within access. Is there a way to do this without
having to resource each table individually?

Thanks
Nick

Create the first PT (using all the column you will need now and later)
on a new worksheet.

Copy the PT, or the entire PT worksheet to a new worksheet, and change
the new PT as needed.

Rinse and repeat.

Mike Alexander recently discussed this he
http://datapigtechnologies.com/blog/...books-in-half/

if you use an ADODB recordset populated from the database table, you can
create an pivotcache then use this to create multiple pivot tables ...


so for an example, i have this in a code module -- do the following if
you're unsure
( ALT+F11,
Insert/Module
Tools/References Microsoft Active DataObjects 2.7 Library
)


Option Explicit
Dim pc As PivotCache
Dim rst As New ADODB.Recordset
Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim i As Long

MyFile = "Risk.mdb"
SQL = "SELECT * FROM BondTable"
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile
rst.Open SQL, con, adOpenStatic
Set pc = ThisWorkbook.PivotCaches.Add(xlExternal)

' create new pivot tables from the same recordset..
CreateThePivotTable "table1"
CreateThePivotTable "table2"

rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Sub

Sub CreateThePivotTable(text As String)

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets.Add()

With pc
Set .Recordset = rst
Set pt = .CreatePivotTable(ws.Range("C5"))
pt.Name = text
End With

End Sub





"Nick Anderson" wrote in message
...
I have a document that has multiple pivot tables. All pivots need to be
resourced to the same table within access. Is there a way to do this
without
having to resource each table individually?

Thanks
Nick