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”