Creating Pivot Tables using VBA

[flyfisher]

My system is Win98SE and Excel97 SR2. I tried to download Colo's add-in but it returned a can't
find page error.

I have a ss with 5800 rows of data in 4 columns headed ItemNo, cases, raws and packs
(these change on a monthly basis).
I would like to create three pivot tables (using vba) to a) sum the cases for each
unique ItemNo b) average the Raws Cost and c)average the Packs Cost.
The first one is okay (I think - it runs anyway) but the second two are giving me
absolute grief and also include a sum function that wrecks the data.
Here is the current code:

"reportdata" is the named range of 4 columns * 5800 rows of data
dim PT as PivotTable

'CreatePivotTable for Sum of Cases and name ranges
[G1].Select
Selection.Name = "C_PT"
Set PT = ActiveSheet.PivotTableWizard(SourceType:=xlDatabase, SourceData:= _
"reportdata", TableDestination:="C_PT", TableName:="PivotTable1")

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="ItemNo"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cases").Orientation = _
xlDataField
Range([G3], [H3].End(xlDown).Offset(-1, 0)).Select
Selection.Name = "cases"

'CreatePivotTable for Average of Raws and name that range
[J1].Select
Selection.Name = "R_PT"
Set PT = ActiveSheet.PivotTableWizard(SourceType:=xlDatabase, SourceData:= _
"reportdata", TableDestination:="R_PT", TableName:="PivotTable2")

ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="ItemNo"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Raws").Orientation = _
xlDataField
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Raws")
.Orientation = xlDataField
.Name = "Average of Raws"
.Position = 2
.Function = xlAverage
End With
Range([J3], [K3].End(xlDown).Offset(-1, 0)).Select
Selection.Name = "raws"

'CreatePivotTable for Average of Packs and name that range
[M1].Select
Selection.Name = "P_PT"
Set PT = ActiveSheet.PivotTableWizard(SourceType:=xlDatabase, SourceData:= _
"reportdata", TableDestination:="P_PT", TableName:="PivotTable3")

ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="ItemNo"
ActiveSheet.PivotTables("PivotTable3").PivotFields("Raws").Orientation = _
xlDataField
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Packs")
.Orientation = xlDataField
.Name = "Average of Raws"
.Position = 2
.Function = xlAverage
End With
Range([M3], [N3].End(xlDown).Offset(-1, 0)).Select
Selection.Name = "packs"

Thanks in advance to anyone who can assist me.

 

[Mark W.]

Just curious... why are you using VBA to create these PivotTables?

 

[flyfisher]

I am automating a task that generally takes me two dyas a month in setting up the file for my manager. It extracts data fom three separate data sources and collates them onto one sheet. as you can imagine this becomes quite tedious so I am trying to expand my vba skills and earn a few brownie points.
the end result is a sheet with sixteen tabs of data separated out by site and raws or packs plus a summary sheet. my skills can do all that part but pivot tables are not my forte.

 

[Mark W.]

Why not create a template with embedded external data queries and pre-configured PivotTables?

 

[flyfisher]

good question. but you lost me when you said create preconfigured pivot tables. The external data is already being extracted using external queries.

 

[Mark W.]

So, why don't you use the range names created for your external data as the source range for PivotTables that you setup once?

 

[flyfisher]

Mark - thanks mate, you have made me rethink what I have been doing. I was just being a bit thick for a friday. Have now setup an XLT called by a menu button. The xlt contains the external data queries and some of the data with the preconfigured pivot tables. It just needed someone else to look over my shoulder. Once again thanks as this has just made my day.