Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot Tables
I'm trying to write code that will create a pivot table in a seperat worksheet. I recorded a macro that creates the pivot that I want, the edited a bit in the VBA editor. However, now when I try to execute th code Excel returns the message: Run-time error '1004': Unable to get the PivotTables property of the Worksheet class Please find my code below. I would greatly appreciate any help resolving this problem. Thanks, Peter Sub LScorecard() Dim PivotNmRows As Integer Sheets.Add ActiveSheet.Name = "Scorecard" Range("A1").Select Sheets("Total").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select PivotNmRows = Selection.Count ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData: _ "'Total'!R1C1:R" & PivotNmRows & "C25").CreatePivotTabl TableDestination:= _ "'[Loss Reduction Worksheet - Working.xls]Scorecard'!R1C1" TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array _ "Policy Date", "Claim Type", "Claim Status") With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Tota Incurred") .Orientation = xlDataField .Caption = "Count of Claims" .Position = 1 .Function = xlCount End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Tota Paid") .Orientation = xlDataField .Caption = "Sum of Total Paid" .Position = 2 .Function = xlSum End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Tota Outstanding") .Orientation = xlDataField .Caption = "Sum of Total Outstanding" .Position = 3 .Function = xlSum End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Tota Incurred") .Orientation = xlDataField .Caption = "Sum of Total Incurred" .Position = 4 .Function = xlSum End With End Su -- PGalla0 ----------------------------------------------------------------------- PGalla06's Profile: http://www.excelforum.com/member.php...fo&userid=2426 View this thread: http://www.excelforum.com/showthread.php?threadid=40087 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot Tables
What line of code is crashing?
-- HTH... Jim Thomlinson "PGalla06" wrote: I'm trying to write code that will create a pivot table in a seperate worksheet. I recorded a macro that creates the pivot that I want, then edited a bit in the VBA editor. However, now when I try to execute the code Excel returns the message: Run-time error '1004': Unable to get the PivotTables property of the Worksheet class Please find my code below. I would greatly appreciate any help resolving this problem. Thanks, Peter Sub LScorecard() Dim PivotNmRows As Integer Sheets.Add ActiveSheet.Name = "Scorecard" Range("A1").Select Sheets("Total").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select PivotNmRows = Selection.Count ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Total'!R1C1:R" & PivotNmRows & "C25").CreatePivotTable TableDestination:= _ "'[Loss Reduction Worksheet - Working.xls]Scorecard'!R1C1", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _ "Policy Date", "Claim Type", "Claim Status") With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Incurred") .Orientation = xlDataField .Caption = "Count of Claims" .Position = 1 .Function = xlCount End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Paid") .Orientation = xlDataField .Caption = "Sum of Total Paid" .Position = 2 .Function = xlSum End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Outstanding") .Orientation = xlDataField .Caption = "Sum of Total Outstanding" .Position = 3 .Function = xlSum End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Incurred") .Orientation = xlDataField .Caption = "Sum of Total Incurred" .Position = 4 .Function = xlSum End With End Sub -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=400873 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot Tables
The debugger stops at the following line: ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _ "Policy Date", "Claim Type", "Claim Status" -- PGalla0 ----------------------------------------------------------------------- PGalla06's Profile: http://www.excelforum.com/member.php...fo&userid=2426 View this thread: http://www.excelforum.com/showthread.php?threadid=40087 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot Tables
Hi Peter,
Try changing the way you reference the source data. Dim a range and set that range equal to the source range for your table. In the SourceData parameter enter the address of the range you Dimed. Don't add the first sheet that you name Scorecard. Leave TableDestination:="" like that. Excel will automatically add a sheet for the table and make it the activesheet. Just rename the activesheet and avoid that long string the the TableDestination. Something like this should work. dim rng as range sheets("total").activate range("a1").select Set rng = ActiveCell.CurrentRegion ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ rng.Address).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Name = "Scorecard" Regards, Mike "PGalla06" wrote: I'm trying to write code that will create a pivot table in a seperate worksheet. I recorded a macro that creates the pivot that I want, then edited a bit in the VBA editor. However, now when I try to execute the code Excel returns the message: Run-time error '1004': Unable to get the PivotTables property of the Worksheet class Please find my code below. I would greatly appreciate any help resolving this problem. Thanks, Peter Sub LScorecard() Dim PivotNmRows As Integer Sheets.Add ActiveSheet.Name = "Scorecard" Range("A1").Select Sheets("Total").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select PivotNmRows = Selection.Count ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Total'!R1C1:R" & PivotNmRows & "C25").CreatePivotTable TableDestination:= _ "'[Loss Reduction Worksheet - Working.xls]Scorecard'!R1C1", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _ "Policy Date", "Claim Type", "Claim Status") With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Incurred") .Orientation = xlDataField .Caption = "Count of Claims" .Position = 1 .Function = xlCount End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Paid") .Orientation = xlDataField .Caption = "Sum of Total Paid" .Position = 2 .Function = xlSum End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Outstanding") .Orientation = xlDataField .Caption = "Sum of Total Outstanding" .Position = 3 .Function = xlSum End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Incurred") .Orientation = xlDataField .Caption = "Sum of Total Incurred" .Position = 4 .Function = xlSum End With End Sub -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=400873 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot Tables
Add this line of code to your procedure and lets see if we get any further...
dim myPivot as pivottable 'Ath the very top of teh preocedure set myPivot = ActiveSheet.PivotTables("PivotTable1") myPivot.AddFields RowFields:=Array( _ "Policy Date", "Claim Type", "Claim Status") Then re-run your code. (I am looking to see if we can reference the pivot table created before adding the items) -- HTH... Jim Thomlinson "PGalla06" wrote: The debugger stops at the following line: ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _ "Policy Date", "Claim Type", "Claim Status") -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=400873 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Views within pivot tables | Excel Discussion (Misc queries) | |||
creating a pivot table from 4 pivot tables | Excel Worksheet Functions | |||
Creating Pivot Tables | Excel Discussion (Misc queries) | |||
Creating Pivot Tables using a CSV file | Excel Discussion (Misc queries) | |||
Creating multiple pivot tables from same cache | Excel Programming |