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

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

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

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

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