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