Creating a Pivot Table with a named range

[tkpmep]

I'm having trouble creating a Pivot Table with a named range. My code
follows, and guidance on the cause of my error will be greatly
appreciated.


Sub CreateHeadersAndPivotTable()
Dim PTCache As PivotCache
Dim PT1 As PivotTable

Col = 15


Range(Sheet23.Cells(1, Col), Sheet23.Cells(1,
Col).End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Name = "Data"

Set PTCache =
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,
SourceData:="Data")
Set PT1 =
ActiveWorkbook.ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _
TableDestination:=Sheet23.Cells(5, Col +
10))

end sub

It's not clear to me if I create a range named Data, because the code
stosp at the next line (Set PTCache = ..) with the error message Run
time errror 438, Object doesn't support this porperty or method.

Thanks in advance for your assistance.

Sincerely

Thomas Philips

 

[tkpmep]

It seems I have a partial solution: I had to ADD, not CREATE a
PivotCache, i.e. I can get the following line to work:
Set PTCache =
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:="Data")

Moreover, the range data is good - I can for example create a watch on
Range("Data").Cells(1,1).Value, and I get the rgiht result

But the next line still results in an error: Run-time Error 1004:
Application Defined or object-defined error.

ActiveWorkbook.ActiveSheet.PivotTables.Add(PivotCache:=PTCache,
TableDestination:=Sheet23.Cells(5, Col + 10))

Any thoughts? googling has not got me an answer.

Sincerely

Thomas Philips

 

[tkpmep]

Got the rest of it - I had to clear the existing pivot table as the
new one was overwriting it. Had to run the following code before
creating the pivot table:

With Sheet23
For Each p In .PivotTables
p.TableRange2.Clear
Next
End With

 

[ryguy7272]

I encountered a similar issue recently.
Check this out
http://www.microsoft.com/office/com...45ec26125cf&cat=&lang=en&cr=US&sloc=en-us&p=1

Your situation will be slightly different, but I believe the concept is the
same.

Regards,
Ryan----