|
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? |
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 |
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 |
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 |
| All times are GMT +1. The time now is 11:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com