pivot table destination

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Is it possible to create a pivot table on a new sheet but to choose its position ? Like you can choose when it is on the same worksheet.
Is it with .CreatePivotTable TableDestination:="" ?

Cheers,
Douvid
 
The following code creates a new sheet, and creates the PT at cell A3:

'=============================
Sub PivotTableNewSheet()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Set ws1 = ActiveSheet
Set wsNew = Worksheets.Add

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Database").CreatePivotTable _
TableDestination:=wsNew.Range("A3"), _
TableName:="PivotTable1"
wsNew.PivotTables("PivotTable1").AddFields _
RowFields:="Year", ColumnFields:="Region"
wsNew.PivotTables("PivotTable1").PivotFields("Units") _
.Orientation = xlDataField
ws1.Select
End Sub
'=============================
 
Back
Top