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

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

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

I encountered a similar issue recently.
Check this out
http://www.microsoft.com/office/comm...sloc=en-us&p=1

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

Regards,
Ryan----

--
RyGuy


"(E-Mail Removed)" wrote:

> 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
>