D
David Howdon
What I am trying to do is to use VBA to create a pivot table and place
it in a particular named sheet. Playing with recording pivot table
creation and tweaking that slightly I have got to
Dim strPivotName As String
strPivotName = "Pivot"
ActiveWorkbook.Sheets.Add().Name = strPivotName
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Range("a1").CurrentRegion).CreatePivotTable
TableDestination:="", TableName:= _
"PivotResults", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=Sheets(strPivotName).Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotResults").AddFields RowFields:="Code", _
ColumnFields:="Week"
With ActiveSheet.PivotTables("PivotResults").PivotFields("Q")
.Orientation = xlDataField
.Caption = "Sum of Q"
.Function = xlSum
End With
Apart from being incredibly ugly code this nearly does what I want. It
creates the pivot table and puts it in the appropriately named sheet.
However it also create a spurious blank sheet.
I suspect this is something to do with the TableDestination:="" but
replacing that with TableDestination:=Sheets(strPivotName causes an error.
What am I doing wrong?
it in a particular named sheet. Playing with recording pivot table
creation and tweaking that slightly I have got to
Dim strPivotName As String
strPivotName = "Pivot"
ActiveWorkbook.Sheets.Add().Name = strPivotName
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Range("a1").CurrentRegion).CreatePivotTable
TableDestination:="", TableName:= _
"PivotResults", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=Sheets(strPivotName).Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotResults").AddFields RowFields:="Code", _
ColumnFields:="Week"
With ActiveSheet.PivotTables("PivotResults").PivotFields("Q")
.Orientation = xlDataField
.Caption = "Sum of Q"
.Function = xlSum
End With
Apart from being incredibly ugly code this nearly does what I want. It
creates the pivot table and puts it in the appropriately named sheet.
However it also create a spurious blank sheet.
I suspect this is something to do with the TableDestination:="" but
replacing that with TableDestination:=Sheets(strPivotName causes an error.
What am I doing wrong?