ripkin900
01-02-2012, 07:15 AM
Hello, I am kind of new at VBA...
I am doing a create pivot table macro...
When I record the macro using Macro recorder it gives me this:
With ActiveSheet.PivotTables("PivotTable12").PivotFields("Tool No.")
.Orientation = xlRowField
.Position = 0
End With
With ActiveSheet.PivotTables("PivotTable12").PivotFields("Arr Wid")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable12").PivotFields("Arr Len")
.Orientation = xlRowField
.Position = 2
End With
Which works fine...
I then try to use it in a Macro using a PV table cache and it does not work...
Sub MakePivotTableforTool_Width_Lenght()
Dim PTCACHE As PivotCache
Dim PT As pivottable
' Create the cache
Set PTCACHE = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
' Add a new Worksheet
Worksheets.Add
' Create the Pivot Table
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCACHE, TableDestination:=Range("a3"))
' Specify the Fields
With PT
.PivotFields("Tool No.").Orientation = xlRowField
.Position = 0
.PivotFields("Ext Pan Len").Orientation = xlRowField
.Position = 1
.PivotFields("Ext Pan Wid").Orientation = xlRowField
.Position = 2
End With
At the line .Position=0 gives me a run time error 438...
Object property does not support this method....
Can ANYONE help me with this, it does not seem like it shouldn't work..
I am using excel 2010 in compatability mode because the PT is going to be used in Excel 2003 at my work
I am doing a create pivot table macro...
When I record the macro using Macro recorder it gives me this:
With ActiveSheet.PivotTables("PivotTable12").PivotFields("Tool No.")
.Orientation = xlRowField
.Position = 0
End With
With ActiveSheet.PivotTables("PivotTable12").PivotFields("Arr Wid")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable12").PivotFields("Arr Len")
.Orientation = xlRowField
.Position = 2
End With
Which works fine...
I then try to use it in a Macro using a PV table cache and it does not work...
Sub MakePivotTableforTool_Width_Lenght()
Dim PTCACHE As PivotCache
Dim PT As pivottable
' Create the cache
Set PTCACHE = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
' Add a new Worksheet
Worksheets.Add
' Create the Pivot Table
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCACHE, TableDestination:=Range("a3"))
' Specify the Fields
With PT
.PivotFields("Tool No.").Orientation = xlRowField
.Position = 0
.PivotFields("Ext Pan Len").Orientation = xlRowField
.Position = 1
.PivotFields("Ext Pan Wid").Orientation = xlRowField
.Position = 2
End With
At the line .Position=0 gives me a run time error 438...
Object property does not support this method....
Can ANYONE help me with this, it does not seem like it shouldn't work..
I am using excel 2010 in compatability mode because the PT is going to be used in Excel 2003 at my work