Pivot table field name is not valid

[Dwarakesh]

Hello folks,

I am trying to run a pivot table with the folowing code. The first part runs good, the problem is in the second part where I try to create another pivot table in a new sheet. It throws me an error saying Pivot table field name is not valid (Run time error 1004). The highlighted part below is were VBA throws the error. Any help regarding this is highly appreciated. Thanks in advance!

Sub addCache()
Dim myRow As Long
Dim myCell As Range
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"

Set myCell = Worksheets("Pivot").Range("A20")
myRow = myCell.CurrentRegion.Rows.Count + myCell.Row

ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, _
SourceData:="report (2)!R8C1:R60000C20").CreatePivotTable _
TableDestination:=Worksheets("Pivot").Range("A" & myRow + 3), _
TableName:="OvertimeHours"
'wsNew.Name & "!R31C1",

Sheets("Pivot").Activate

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

ActiveSheet.PivotTables("TotalWorkHours").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("OvertimeHours").RowAxisLayout xlTabularRow

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

Columns.AutoFit
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Dim wsNew3 As Worksheet
Set wsNew3 = Sheets.Add
wsNew3.Name = "Pivot Punched In"

ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, _
SourceData:="report (3)!R7C1:R60000C20").CreatePivotTable _
TableDestination:='wsNew3.Name & "!R31C1", _
TableName:="LastStart"

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

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

With .PivotFields("Last Date")
.Orientation = xlPageField
.Position = 1
End With

.AddDataField ActiveSheet.PivotTables("LastStart").PivotFields("Last Start"), _
"Count of Last Start", xlCount

ActiveSheet.PivotTables("LastStart").RowAxisLayout xlTabularRow

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

Columns.AutoFit

Sheets("Pivot").Activate

End With

End Sub