Pivot Table Destination Problem

[swayp11]

I'm having an error when I specify the table destination.

Here is my code:

Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "Pivot Table"

Worksheets("Pivot Table Data").Activate
Range("A1").CurrentRegion.Name = "PivotRange"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:="=PivotRange").CreatePivotTable TableDestination:="Pivot Table!R3C1", TableName:="PivotTable"

When I have TableDestination="", the code works fine.

I'm confused because I'm actually creating that sheet in the code right before creating the pivot table.

 

[xenou]

I would just be cautious with how your refer to ranges and always use the worksheet references with the range names.

Add(SourceType:=xlDatabase, _
    [COLOR="Red"]SourceData:="=PivotRange"[/COLOR]
'//TRY INSTEAD:
Add(SourceType:=xlDatabase, _
    SourceData:=[COLOR="Blue"]Worksheets("Pivot Table Data").Range("PivotRange"))[/COLOR]

Same for:

TableDestination:="[COLOR="Red"]Pivot Table!R3C1[/COLOR]"
'//TRY INSTEAD:
TableDestination:=[COLOR="Blue"]Worksheets("Pivot Table").Range("A3")[/COLOR]

It's not that these other methods are wrong per se, but experiment with names and R1C1 notation some other time

Sub Foo()

Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "Pivot Table"

Worksheets("Pivot Table Data").Activate
Range("A1").CurrentRegion.Name = "PivotRange"

Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:=Worksheets("Pivot Table Data").Range("PivotRange"))
    
PC.CreatePivotTable _
    TableDestination:=Worksheets("Pivot Table").Range("A3"), _
        TableName:="PivotTable"

End Sub

For what its worth, do we really need to add a pivot table repeatedly? Can we have the pivot table in place and just refresh it when the new data arrives?

Regards,
Alex

 

[xenou]

Note: Usually spaces in names require single quotes too but its odd that it worked the first time. Oh well - that is odd...
'Pivot Table'!R3C1

 

[swayp11]

Thank you Alexander.

To answer some of your questions/comments. The R1C1 reference was simply the last thing I tried. The code started to get ugly cause I couldn't get it to work, so I was trying every way I can think of (obviously to no avail).

The Pivot Table won't be added repeatedly. It is mostly an experiment, and is being built off of some commands from a userform.

Thank you for your help!