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:=xlDatab ase, SourceData:= _
"Data").CreatePivotTable TableDestination:= _
"'[DSG B&B Template.xls]Mobile'!PTCell",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=515279

Does naybody know if this is possible


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=515279

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:=xlDatab ase, SourceData:= _
"Data").CreatePivotTable TableDestination:=PTCell, _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

Looks like it would work.

moglione1 wrote:

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:=xlDatab ase, SourceData:= _
"Data").CreatePivotTable TableDestination:= _
"'[DSG B&B Template.xls]Mobile'!PTCell",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=515279

--

Dave Peterson