Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table by Macro? | Charts and Charting in Excel | |||
Is it possible to Macro a Pivot Table? | Excel Worksheet Functions | |||
Pivot Table Macro | Excel Discussion (Misc queries) | |||
MACRO FOR PIVOT TABLE | Excel Discussion (Misc queries) | |||
Macro to change a Pivot Table | Excel Discussion (Misc queries) |