Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Pivot Table with a named range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Pivot Table with a named range
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:=xlDatab ase, 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(PivotCa che:=PTCache, TableDestination:=Sheet23.Cells(5, Col + 10)) Any thoughts? googling has not got me an answer. Sincerely Thomas Philips |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Pivot Table with a named range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Pivot Table with a named range
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a named range? | Excel Discussion (Misc queries) | |||
Pivot Table Wizard Fails to Recognize New Rows of a Named Range | Excel Worksheet Functions | |||
Creating a named range | Excel Programming | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
Creating a pivot table using all named ranges | Excel Programming |