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