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

2810 Views 3 Replies 2 Participants Last post by wally32, Apr 6, 2011

wally32

Discussion Starter · Mar 31, 2011 (Edited)

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

Save

Status: Not open for further replies.

1 - 4 of 4 Posts

koala

· #2 · Mar 31, 2011

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

Save

wally32

Discussion Starter · #3 · Apr 6, 2011

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

See less See more

Save

wally32

Discussion Starter · #4 · Apr 6, 2011

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

See less See more

Save

1 - 4 of 4 Posts

Status: Not open for further replies.

Top Contributors this Month