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


" 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:=xlDa tabase,
SourceData:="Data")
Set PT1 =
ActiveWorkbook.ActiveSheet.PivotTables.Add(PivotCa che:=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