Slicemahn
09-04-2007, 07:08 AM
Hello Everyone,
I have stepped through this code many times and still haven't a clue why the code does not execute.
I get an error 438: Object doesn't support this property or method. It occurs at the line:
Orientation=xlDataField
I would appreciate any thoughts or suggestions to execute this code.
Sub PivotTableCreation()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim rngSource As Range
Dim Wks As Worksheet
Application.ScreenUpdating = False
'Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivotdata").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Sheets("ExcelView").Range("A1").CurrentRegion.Address)
'Add new worksheet
Set Wks = Worksheets.Add
Wks.Name = "Pivotdata"
'Create the pivot table from the cache
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Wks.Range("A1"), _
TableName:="AmansPivot")
With PT
'Add fields
.AddFields RowFields:=Array("TFN", "Area Code", "revisedDate")
End With
With PT
.PivotFields ("Area Code")
.Orientation = xlDataField
.Caption = "Count of Area Code"
.Function = xlCount
.TableRange1.EntireColumn.AutoFit
End With
PT.PivotSelect "'Area Code'[All;Total]", xlDataAndLabel, True
Selection.Delete
PT.PivotSelect "TFN[All;Total]", xlDataAndLabel, True
Selection.Delete
PT.TableRange2.Offset(1, 0).Copy
Sheets("Data").Range("B5").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("B5").Select
' Clear the original Pivot Table & the Pivot Cache
PT.TableRange2.Clear
Set PTCache = Nothing
End Sub
I have stepped through this code many times and still haven't a clue why the code does not execute.
I get an error 438: Object doesn't support this property or method. It occurs at the line:
Orientation=xlDataField
I would appreciate any thoughts or suggestions to execute this code.
Sub PivotTableCreation()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim rngSource As Range
Dim Wks As Worksheet
Application.ScreenUpdating = False
'Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivotdata").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Sheets("ExcelView").Range("A1").CurrentRegion.Address)
'Add new worksheet
Set Wks = Worksheets.Add
Wks.Name = "Pivotdata"
'Create the pivot table from the cache
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Wks.Range("A1"), _
TableName:="AmansPivot")
With PT
'Add fields
.AddFields RowFields:=Array("TFN", "Area Code", "revisedDate")
End With
With PT
.PivotFields ("Area Code")
.Orientation = xlDataField
.Caption = "Count of Area Code"
.Function = xlCount
.TableRange1.EntireColumn.AutoFit
End With
PT.PivotSelect "'Area Code'[All;Total]", xlDataAndLabel, True
Selection.Delete
PT.PivotSelect "TFN[All;Total]", xlDataAndLabel, True
Selection.Delete
PT.TableRange2.Offset(1, 0).Copy
Sheets("Data").Range("B5").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("B5").Select
' Clear the original Pivot Table & the Pivot Cache
PT.TableRange2.Clear
Set PTCache = Nothing
End Sub