I'm not sure about the particular problem you're having.
But generally speaking, you can build a pivot table with
code. My example is of a pivot table built from data in an
access database, but I think you'll get the general idea.
tod
Sub MakePT()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim PTSheet As Worksheet
Dim PT As PivotTable
Dim sql As String
Dim PC As PivotCache
'Just the sheet name where my pivot table is going to
be created
Set PTSheet = ThisWorkbook.Worksheets("My Pivot Table
Sheet")
'Connects to Access database and runs the given query.
Puts results in recordset.
cn.Open "Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\Sample Database.mdb;"
sql = "SELECT * FROM qrySample"
rs.Open sql, cn
'Use resulting recordset to create pivot table cache.
Set PC = ThisWorkbook.PivotCaches.Add
(SourceType:=xlExternal)
Set PC.Recordset = rs
'This example puts the new pivot table on the sheet
with the top-left corner in A9
Set PT = PTSheet.PivotTables.Add(PivotCache:=PC,
TableDestination:=PTSheet.Range("A9"))
'Fills in the values and formatting
With PT
.SmallGrid = False
'In the RowFields, Data represents all of your
Data fields
.AddFields PageFields:=Array("Client
Name", "Client Group", "City"), _
RowFields:=Array("Report
Month", "Priority", "Data")
'This moves those data fields to show them
horizontally
With .PivotFields("Issues Open")
.Orientation = xlDataField
End With
With .PivotFields("Issues Pending")
.Orientation = xlDataField
End With
'Formats the whole pivot table using AutoFormat
number 6
.Format xlReport6
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
-----Original Message-----
Is it possible to automate the geration of pivot tables from within a maco?
When I generate a pivot table for a user it opens in a
small window and the user has to click on the edit pivot
table and then update the data in the table to veiw the up
to date data.
