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