Creating Pivot Tables

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:=xlDatabase, 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

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:=xlDatabase, 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
>
>

The debugger stops at the following line:

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"Policy Date", "Claim Type", "Claim Status")

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:=xlDatabase, 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:=xlDatabase, 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
>
>

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
>
>