Pivot Table from Macro
 

I am trying to produce a pivot table using a macro.

I have the following:

Sub Macro4()
'
' Macro4 Macro
'
Lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
Range("A1").PivotCaches.Add(SourceType:=xlDatabase ,
SourceData:="'Average Deployment'!R1:R" & Lastrow).CreatePivotTable
TableDestination:="", TableName:="PivotTable10",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable10").AddFields
RowFields:=Array("Year", "Quarter")
With ActiveSheet.PivotTables("PivotTable10").PivotField s("Number of Days")
.Orientation = xlDataField
.Caption = "Average of Number of Days"
.Function = xlAverage
End With
Range("A4").Group Start:=True, End:=True, Periods:=Array(False, False,
False, _
False, False, False, True)
Columns("C:C").NumberFormat = "0"
End Sub

But I get error: Run Time Error 438, Object doesn't support this property or
method.

Can anyone tell me where I am going wrong?

Thanks
Dave

Pivot Table from Macro
 

Which line generates the error?
--
HTH...

Jim Thomlinson


"Dave" wrote:

I am trying to produce a pivot table using a macro.

I have the following:

Sub Macro4()
'
' Macro4 Macro
'
Lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
Range("A1").PivotCaches.Add(SourceType:=xlDatabase ,
SourceData:="'Average Deployment'!R1:R" & Lastrow).CreatePivotTable
TableDestination:="", TableName:="PivotTable10",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable10").AddFields
RowFields:=Array("Year", "Quarter")
With ActiveSheet.PivotTables("PivotTable10").PivotField s("Number of Days")
.Orientation = xlDataField
.Caption = "Average of Number of Days"
.Function = xlAverage
End With
Range("A4").Group Start:=True, End:=True, Periods:=Array(False, False,
False, _
False, False, False, True)
Columns("C:C").NumberFormat = "0"
End Sub

But I get error: Run Time Error 438, Object doesn't support this property or
method.

Can anyone tell me where I am going wrong?

Thanks
Dave

Pivot Table from Macro
 

http://www.excelforum.com/showthread...=1#post1852140

I think my problem has been fixed here.
Thanks Guys for your time.

"Jim Thomlinson" wrote:

Which line generates the error?
--
HTH...

Jim Thomlinson


"Dave" wrote:

I am trying to produce a pivot table using a macro.

I have the following:

Sub Macro4()
'
' Macro4 Macro
'
Lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
Range("A1").PivotCaches.Add(SourceType:=xlDatabase ,
SourceData:="'Average Deployment'!R1:R" & Lastrow).CreatePivotTable
TableDestination:="", TableName:="PivotTable10",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable10").AddFields
RowFields:=Array("Year", "Quarter")
With ActiveSheet.PivotTables("PivotTable10").PivotField s("Number of Days")
.Orientation = xlDataField
.Caption = "Average of Number of Days"
.Function = xlAverage
End With
Range("A4").Group Start:=True, End:=True, Periods:=Array(False, False,
False, _
False, False, False, True)
Columns("C:C").NumberFormat = "0"
End Sub

But I get error: Run Time Error 438, Object doesn't support this property or
method.

Can anyone tell me where I am going wrong?

Thanks
Dave