Hi Guys

I am automating with VBA a process which creates a pivot table.

The problem i have is that I would like to set the destination of th
new pivot table to a specific sheet instead of "sheet1" (or the nex
number as happens by default). I tried using the option "use existin
sheet" in the pivot sheet creation dialogue box however no VBA cod
appears to be created for assigning the pivot table to a specificall
named pivot sheet. i.e. the new pivot sheet always tries to put itsel
on "Sheet1".

Any suggestions ?

Thanks in advance

--
paz2
-----------------------------------------------------------------------
paz24's Profile: http://www.excelforum.com/member.php...fo&userid=3104
View this thread: http://www.excelforum.com/showthread.php?threadid=50761

Paz,


For either the

PivotCache.CreatePivotTable

or the

PivotCaches.Add(....).CreatePivotTable

methods, set the TableDestination property.

Use something like this, if you have sheet's codename, but the user can change the sheet name:

TableDestination:="'" & Sheet4.Name & "'!R2C8"

Or, if you have a named that you are sure is the

TableDestination:="'Sheet Name'!R2C8"

etc..

HTH,
Bernie
MS Excel MVP


"paz24" wrote in message
...

Hi Guys

I am automating with VBA a process which creates a pivot table.

The problem i have is that I would like to set the destination of the
new pivot table to a specific sheet instead of "sheet1" (or the next
number as happens by default). I tried using the option "use existing
sheet" in the pivot sheet creation dialogue box however no VBA code
appears to be created for assigning the pivot table to a specifically
named pivot sheet. i.e. the new pivot sheet always tries to put itself
on "Sheet1".

Any suggestions ?

Thanks in advance.


--
paz24
------------------------------------------------------------------------
paz24's Profile: http://www.excelforum.com/member.php...o&userid=31046
View this thread: http://www.excelforum.com/showthread...hreadid=507617