Solved: Pivot Table Creation: Error 438

[Slicemahn]

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

[rory]

You are trying to set the Orientation of the Pivot table, rather than a pivotfield within it, which is why your code fails.

[Slicemahn]

Thanks Rory for the second set of eyes! I don't know how I overlooked it.

Cheers
Slice