Resourcing Multiple Pivot Tables
 

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

Resourcing Multiple Pivot Tables
 

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/

Resourcing Multiple Pivot Tables
 

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