VBA for pivot tables
 

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:=xlDatab ase, 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").PivotField s("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?


--
Each day a man watched a donkey walk past a high wood fence with one
plank removed. Each day he saw a nose, then the ears, then the neck,
forequarters, back and finally the tail. He pondered this for a time
and eventually declared. “I understand now. The nose causes the tail”

VBA for pivot tables
 

You can create a string with the sheet name and destination cell, e.g.:

'====================
Sub test()

Dim strPivotName As String
Dim wsPivot As Worksheet
Dim wsData As Worksheet
Dim strDest As String

strPivotName = "Pivot"
Set wsPivot = ActiveWorkbook.Sheets.Add
wsPivot.Name = strPivotName
strDest = "'" & strPivotName & "'!R5C3"
Set wsData = Sheets("Data")

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
wsData.Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:=strDest, TableName:= _
"PivotResults", DefaultVersion:=xlPivotTableVersion10
With wsPivot
.PivotTables("PivotResults").AddFields RowFields:="Code", _
ColumnFields:="Week"
With .PivotTables("PivotResults").PivotFields("Q")
.Orientation = xlDataField
.Caption = "Sum of Q"
.Function = xlSum
End With
End With
End Sub

'=================================

David Howdon wrote:
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:=xlDatab ase, 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").PivotField s("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?




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

VBA for pivot tables
 

Debra Dalgleish wrote:
You can create a string with the sheet name and destination cell, e.g.:

[snip]

Thanks this worked.

--
Each day a man watched a donkey walk past a high wood fence with one
plank removed. Each day he saw a nose, then the ears, then the neck,
forequarters, back and finally the tail. He pondered this for a time
and eventually declared. “I understand now. The nose causes the tail”