For Example:

TableDestination:=Worksheets("'SheetRef'").Range(" K4")

HTH,
Bernie
MS Excel MVP


"Hru48" wrote in message
...

Hey,

I'm having a lot of trouble with pivot tables. I am trying to get a
macro to create them automatically by taking data from one sheet and
making a pivot table in another ('SheetRef').

I can get it to work if I create a blank sheet for each new pivot by
changing the table destination to "". But when I try to specify that I
want them to be created on a specific sheet I get an error.

I have been trying to put the sheet name in the table desitnation:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'01'!R8C1:R477C2").CreatePivotTable TableDestination:= HERE,
_
TableName:="PivotTable1"

Am I doing something wrong? If someone can point me in the right
direction I would really appreciate it. I have put the whole recording
of createing th pivot underneath if that helps?

Cheers

Hayley



Range("A8:B8").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'01'!R8C1:R477C2").CreatePivotTable
TableDestination:=Range("G1"), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Agent
Number/Name")
Orientation = xlRowField
Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Campaign
Type")
Orientation = xlDataField
Position = 1
End With
Application.CommandBars("PivotTable").Visible = False
End Sub


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821