Pivot table Destination

[moglione1]

Hi all,

I am trying to create a pivot table in a specific cell on a sheet. The
code I have is below. Basically I want to create pivot table so it
appears in the cell I have named as PTCell. PLEASE HELP as this does
not work

Private Sub MovPivot()
Dim PTCell As Range

'Pass heading to a String variable
'strField = Selection.Cells(1, 1).Text

'Name the list range
Sheets("Mobile").Activate
ActiveSheet.Range("A1").Select
ActiveCell.End(xlToRight).End(xlDown).Select
Range(ActiveCell, "A1").Select
Selection.Name = "Data"

Range("A1").Select

ActiveCell.End(xlDown).Offset(2, 0).Select
Set PTCell = ActiveCell

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data").CreatePivotTable TableDestination:= _
"'[DSG B&B Template.xls]Mobile'!PTCell",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

 

[moglione1]

Does naybody know if this is possible

 

[Dave Peterson]

I'm guessing that PTCell is a cell in a different workbook.

If that's true, then take a look at VBA's help for .CreatePivotTable. You'll
see this line:

The destination range must be on a worksheet in the workbook that contains the
PivotCache object specified by expression.

=======
If that PTCell is in the same workbook, then:

Dim PTCell as range
.....

Set PTCell _
= activeworkbook.Worksheets("Mobile").Range("PTCell")
.....

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data").CreatePivotTable TableDestination:=PTCell, _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

Looks like it would work.

Hi all,

I am trying to create a pivot table in a specific cell on a sheet. The
code I have is below. Basically I want to create pivot table so it
appears in the cell I have named as PTCell. PLEASE HELP as this does
not work

Private Sub MovPivot()
Dim PTCell As Range

'Pass heading to a String variable
'strField = Selection.Cells(1, 1).Text

'Name the list range
Sheets("Mobile").Activate
ActiveSheet.Range("A1").Select
ActiveCell.End(xlToRight).End(xlDown).Select
Range(ActiveCell, "A1").Select
Selection.Name = "Data"

Range("A1").Select

ActiveCell.End(xlDown).Offset(2, 0).Select
Set PTCell = ActiveCell

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data").CreatePivotTable TableDestination:= _
"'[DSG B&B Template.xls]Mobile'!PTCell",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10