| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table in VB
I have the following code I tried to use to create Pivot table.
Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable Worksheets.Add ActiveSheet.Name = "PivotSheet" Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=Range("A1").CurrentRegion.Address) Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") With PT ..PivotFields("Month").Orientation = xlPageField ..PivotFields("Control Number").Orientation = xlColumnField ..PivotFields("Company Code").Orientation = xlRowField ..PivotFields("Tax Payments").Orientation = xlDataField End With End Sub It gives me "error 1004" and highlights the line: Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") What have I done wrong? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot table in VB
Hi
The problem is that after creating the new Worksheet, it is the active sheet and the PT Cache is being set to a region which is just A1 of that sheet. You need to activate the sheet with your source data, after creating the new sheet and before setting the PT Cache. -- Regards Roger Govier "ake" wrote in message ... I have the following code I tried to use to create Pivot table. Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable Worksheets.Add ActiveSheet.Name = "PivotSheet" Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=Range("A1").CurrentRegion.Address) Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") With PT .PivotFields("Month").Orientation = xlPageField .PivotFields("Control Number").Orientation = xlColumnField .PivotFields("Company Code").Orientation = xlRowField .PivotFields("Tax Payments").Orientation = xlDataField End With End Sub It gives me "error 1004" and highlights the line: Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") What have I done wrong? __________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot table in VB
Thanks for this, please be more specific, im lost...
"Roger Govier" wrote: Hi The problem is that after creating the new Worksheet, it is the active sheet and the PT Cache is being set to a region which is just A1 of that sheet. You need to activate the sheet with your source data, after creating the new sheet and before setting the PT Cache. -- Regards Roger Govier "ake" wrote in message ... I have the following code I tried to use to create Pivot table. Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable Worksheets.Add ActiveSheet.Name = "PivotSheet" Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=Range("A1").CurrentRegion.Address) Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") With PT .PivotFields("Month").Orientation = xlPageField .PivotFields("Control Number").Orientation = xlColumnField .PivotFields("Company Code").Orientation = xlRowField .PivotFields("Tax Payments").Orientation = xlDataField End With End Sub It gives me "error 1004" and highlights the line: Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") What have I done wrong? __________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot table in VB
Hi
Assuming your data is on Sheet1 Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable Worksheets.Add ActiveSheet.Name = "PivotSheet" Sheets("Sheet1").Activate Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=Range("A1").CurrentRegion.Address) Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") You need to make whatever the sheet with your data is, as the active sheet before setting the PTCache with the line Sheets("Sheet1").Activate Change "Sheet1" to wahtever your sheet is called. -- Regards Roger Govier "ake" wrote in message ... Thanks for this, please be more specific, im lost... "Roger Govier" wrote: Hi The problem is that after creating the new Worksheet, it is the active sheet and the PT Cache is being set to a region which is just A1 of that sheet. You need to activate the sheet with your source data, after creating the new sheet and before setting the PT Cache. -- Regards Roger Govier "ake" wrote in message ... I have the following code I tried to use to create Pivot table. Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable Worksheets.Add ActiveSheet.Name = "PivotSheet" Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=Range("A1").CurrentRegion.Address) Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") With PT .PivotFields("Month").Orientation = xlPageField .PivotFields("Control Number").Orientation = xlColumnField .PivotFields("Company Code").Orientation = xlRowField .PivotFields("Tax Payments").Orientation = xlDataField End With End Sub It gives me "error 1004" and highlights the line: Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets( "PivotSheet").Range("A1"), TableName:=" PivotTable") What have I done wrong? __________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4246 (20090715) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4247 (20090715) __________ The message was checked by ESET Smart Security. http://www.eset.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| Pivot Table and Pivot Table dates are not in correct order | Charts and Charting in Excel | |||
| Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
| Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
| Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming | |||
Linear Mode
