.Orientation = xl rowfield - Unable to set Orientation property error

[Dwarakesh]

I am trying to create the following pivot tables on a single sheet named Pivot. I extract data from another workbook and then run pivot for the exported data. The Highlighted red (below code) line throws 'Unable to set orientation property error'. The Department Level(1) column has varied row data as it is exported daily from another database. Please help me fix this error.

Thanks in advance!
-------------------------------------------------------------------------------------------------------------------

Sub addCache()
Dim wsNew As Worksheet


Set wsNew = Sheets.Add
wsNew.Name = "Pivot"




ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, _
SourceData:="report (2)!R8C1:R60000C20").CreatePivotTable _
TableDestination:=wsNew.Name & "!R1C1", _
TableName:="TotalWorkHours"


ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, _
SourceData:="report (2)!R8C1:R60000C20").CreatePivotTable _
TableDestination:=wsNew.Name & "!R21C1", _
TableName:="OvertimeHours"


With ActiveSheet.PivotTables("TotalWorkHours").PivotFields("Pay Calc Profile")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("TotalWorkHours").PivotFields("Date")
.Orientation = xlColumnField
.Position = 2
End With
With ActiveSheet.PivotTables("TotalWorkHours").PivotFields("Department(Level 1)")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("TotalWorkHours").AddDataField ActiveSheet.PivotTables( _
"TotalWorkHours").PivotFields("Total Work Hours"), "Count of Total Work Hours", _
xlCount
With ActiveSheet.PivotTables("TotalWorkHours").PivotFields( _
"Count of Total Work Hours")
.Caption = "Sum of Total Work Hours"
.Function = xlSum
End With


With ActiveSheet.PivotTables("OvertimeHours")
With .PivotFields("Pay Calc Profile")
.Orientation = xlColumnField
.Position = 1
End With


With .PivotFields("Date")
.Orientation = xlColumnField
.Position = 2
End With


With .PivotFields("Department(Level 1)")
.Orientation = xlRowField
.Position = 1
End With


.AddDataField ActiveSheet.PivotTables("OvertimeHours").PivotFields("Overtime Hours"), _
"Sum of OvertimeHours", xlSum
End With


Application.Goto Range("A1"), True
Range("B4").Select
ActiveWindow.FreezePanes = True
ActiveWindow.Zoom = 80


Columns.AutoFit






End Sub

 

[Andrew Poulsom]

Can you add the field to the row area manually?

 

[Dwarakesh]

Can you add the field to the row area manually?

The second pivot table works fine with the same fields and for some reason it doesn't work on the first one.

I am able to add it manually if I add the pivot table manually.

Thanks,

Dwarakesh

 

[Andrew Poulsom]

Can you add it manually after your VBA code has created it?

 

[Dwarakesh]

Oh thanks Andrew, I did that and saw that the two reports were overlapping each other. Kicking myself!

Thanks,

Dwarakesh