VBA for pivot tables

[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?

 

[Debra Dalgleish]

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:=xlDatabase, 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]

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

[snip]

Thanks this worked.