[wally32]

hi
im hoping that someone can please help me to solve my little problem. i have got a macro which creates a pivottable.im looking to slightly adjust the pivottable with the macro code. currently the code is

Sub Test()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Range("B1:G1").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet
.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
.PivotTables("PivotTable1").AddFields RowFields:="Appellant", ColumnFields:="Score"
.PivotTables("PivotTable1").PivotFields("Score").Orientation = xlDataField
End With
End Sub

i want to add a field called "region" in the report filter and also want to add another field "prop no" in row field with Appellant".

thanks

 

[koala]

Re: help for excel macro to create pivottable

Unsolved thread waiting for a reply - http://www.techsupportforum.com/forums/f57/macro-excel-automatically-sort-555708.html

 

[wally32]

Re: help for excel macro to create pivottable [moved from Comments forum]

hi

i have managed to come up with the following macro which is what i am looking to do.
Sub Test()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Range("B1:C1").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet
.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Appellant")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Prop no.")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Score")
.Orientation = xlColumnField
.Position = 1
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Score"), "Count of Score", xlCount
End With
End Sub

When i test this macro it works fine but when it come to adding the "score" field into the count of score, it removes the "score" field from the columnfield. dont know why it does this, can anyone please advise a solution to this.

thanks

 

[wally32]

Re: help for excel macro to create pivottable [moved from Comments forum]

with a bit of patience and testing managed to get to what i want. the code i used was below, it may help someone out there
Sub Test()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Range("B1:C1").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet
.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Score"), "Count of Score", xlCount
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Appellant")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Prop no.")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Score")
.Orientation = xlColumnField
.Position = 1
End With