Help:- Excel 2003 Pivot Table VBA

[Airbisk]

Hi,

I have amended the code below to give me a nearly desired pivot table.

Sub Pivot4()

Dim PTCache As PivotCache


Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Data!R1C1:R65536C15")

Set PT = PTCache.CreatePivotTable(TableDestination:="Pivot!R1C1", TableName:="PivotTable")


With PT.PivotFields("Account")
.Orientation = xlRowField
.Position = 1
End With

With PT.PivotFields("Description")
.Orientation = xlRowField
.Position = 2
End With

Sheets("Pivot").Select

With ActiveSheet.PivotTables("PivotTable").PivotFields("Rate")

.Orientation = xlDataField
.Position = 1
.Function = xlMin
.Name = "Min"

End With

With ActiveSheet.PivotTables("PivotTable").PivotFields("Rate")

.Orientation = xlDataField
.Position = 2
.Function = xlMax
.Name = "Max"

End With

End Sub

Firstly, I am trying to create two columns one for min rate and the other for max rate. I have tried adding columns for these but cannot seem to get it right, any help with this will be appreciated.

Secondly when the pivot table is formed each "account" has a subtotal. Is there something I can put in the code to switch this off for all totals?

Thanks in advance for any guidance.

 

[PHV]

Have you tried the macrorecorder ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Airbisk]

Thanks PH,

I always thought excel 2003 didnt like pivot table macro being recorded, well it produced the code which is a bonus but it comes up with a runtime error "Unable to get the PivotFields property of the PivotTable Class"

The code is below. The **** is the line where the error is produced.

Sub Pivot5()

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Data!C1:C13").CreatePivotTable TableDestination:= _
"'Pivot'!R3C1", TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True

**** With ActiveSheet.PivotTables("PivotTable").PivotFields("Account")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Description")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Rate"), "Count of Rate", xlCount
ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Rate"), "Count of Rate2", xlCount
Range("C3").Select
With ActiveSheet.PivotTables("PivotTable").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
Range("C4").Select
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C4").Select
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("PivotTable").PivotFields("Count of Rate").Function = _
xlMin
Range("D4").Select
ActiveSheet.PivotTables("PivotTable").PivotFields("Count of Rate2").Function _
= xlMax
Range("A7").Select
ActiveSheet.PivotTables("PivotTable").PivotFields("Account").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)

With ActiveSheet.PivotTables("PivotTable")
.ColumnGrand = False
.RowGrand = False
End With

End Sub

 

[PHV]

What happens if you replace ActiveSheet with ActiveWorkbook.Sheets("Pivot") ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Airbisk]

Thanks PH,

Youre suggestion didn't work but suing the recorded macro I was able to mix and match the two snippets of code to make a working version.

Sub Pivot4()

Dim PTCache As PivotCache


Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Data!R1C1:R65536C15")

Set PT = PTCache.CreatePivotTable(TableDestination:="Pivot!R1C1", TableName:="PivotTable")


With PT.PivotFields("Account")
.Orientation = xlRowField
.Position = 1
End With

With PT.PivotFields("Description")
.Orientation = xlRowField
.Position = 2
End With

Sheets("Pivot").Select

ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables("PivotTable").PivotFields("Rate"), "Count of Rate", xlCount
ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables("PivotTable").PivotFields("Rate"), "Count of Rate2", xlCount

With ActiveSheet.PivotTables("PivotTable").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable").PivotFields("Count of Rate").Function = xlMin
ActiveSheet.PivotTables("PivotTable").PivotFields("Count of Rate2").Function = xlMax

ActiveSheet.PivotTables("PivotTable").PivotFields("Account").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)

With ActiveSheet.PivotTables("PivotTable")
.ColumnGrand = False
.RowGrand = False
End With

End Sub


Thanks for your help.