Pivot Table from Macro

D

Dave

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").PivotFields("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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

setting up a pivot table via macro? 0
Record Macro to Create Pivot Table 1
Creating Pivot Table with Macro using Multiple Sheets 0
Macro to create pivotchart problems 0
Passing string as array variable (Pivot VBA) 2
Avoiding an extra sheet when creating a pivot table 3
Pivot table with a variable range in the sourcedata field 2
Pivot Table Macro with dynamic source data 1

Top