Pivot table field name is not valid

Dwarakesh

New Member
Joined
Jul 24, 2014
Messages
4
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,208
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top