MACRO Pivot Table 2

[icemanx010]

Hi,

I am having a problem with the code highlighted in RED and its giving me the error "Invalid procedure call or argument".

Please help me on this one everyone. Also if you catch something on the code that creates an error, please let me know.

Sub Sample_Pivot()

Windows("Yahoo Gatekeeper NPS Report.xlsm").Activate
Sheets("Summary").Select

Range("A17").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"pivot_dbase", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Summary!R17C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
Sheets("Summary").Select
Cells(17, 1).Select
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "Resolution Rate", _
"='Resolved Cases'/'# of Surveys'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Resolution Rate"). _
Orientation = xlDataField
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "FCR %", _
"=FCR/'# of Surveys'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("FCR %").Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add _
"Net Promoter Score", _
"=(Promoters /'# of Surveys' )-(Detractors /'# of Surveys' )", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Net Promoter Score"). _
Orientation = xlDataField


With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Work Week")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Team")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Property")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Queue")
.Orientation = xlRowField
.Position = 2
End With

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("# of Surveys"), "Sum of # of Surveys", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Promoters"), "Sum of Promoters", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Detractors"), "Sum of Detractors", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Resolved Cases"), "Sum of Resolved Cases", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("FCR"), "Sum of FCR", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Sum of FCR"), "Sum of FCR", xlSum
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
Range("A24").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Property").PivotItems( _
"Yahoo! Accounts").Position = 1

Range("F17").Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of FCR %" _
).Position = 7
Range("J17").Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of Net Promoter Score").Position = 4


Range("C17").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("# of Surveys").Caption = _
" # of Surveys"

Range("D17").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Promoters").Caption = _
" Promoters"

Range("E17").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("Detractors").Caption = _
" Detractors"
Range("F17").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Net Promoter Score"). _
Caption = " Net Promoter Score"
Range("G17").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Resolved Cases").Caption = _
" Resolved Cases"
Range("H17").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Resolution Rate").Caption _
= " Resolution Rate"
Range("I17").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("FCR").Caption = " FCR"
Range("J17").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("FCR %").Caption = " FCR %"

Range("F18:F29").Select
Selection.NumberFormat = "0.00%"
Range("H18:H29").Select
Selection.NumberFormat = "0.00%"
Range("J18:J29").Select
Selection.NumberFormat = "0.00%"

ActiveSheet.PivotTables("PivotTable1").PivotSelect "Date", xlButton, True
Range("A11:J29").Select
With Selection
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Copy

Range("L11").Select
ActiveSheet.Paste


ActiveSheet.PivotTables("PivotTable2").PivotFields("Queue").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable2").PivotFields("Property").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Property")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Queue")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Agent ID")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields(" Promoters").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable2").PivotFields(" Detractors").Orientation _
= xlHidden
ActiveSheet.PivotTables("PivotTable2").PivotFields(" Resolved Cases"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable2").PivotFields(" FCR").Orientation = _
xlHidden
Range("R11").Select
ActiveSheet.Paste

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Reason")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields(" Promoters").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable3").PivotFields(" Detractors").Orientation _
= xlHidden
ActiveSheet.PivotTables("PivotTable3").PivotFields(" Resolved Cases"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable3").PivotFields(" FCR").Orientation = _
xlHidden

Range("A11:J29").Select
Selection.Copy
Range("AX11").Select
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable6").PivotFields("Month").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Month")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable6").PivotFields(" Promoters").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable6").PivotFields(" Detractors").Orientation _
= xlHidden
ActiveSheet.PivotTables("PivotTable6").PivotFields(" Resolved Cases"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable6").PivotFields(" FCR").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable6").PivotFields("Property").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable6").PivotFields("Queue").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Property")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Queue")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Reason")
.Orientation = xlRowField
.Position = 1
End With
Application.CutCopyMode = False

ActiveSheet.PivotTables("PivotTable6").RowGrand = False
Range("A5").Select
End Sub

Thank you,
ICEMAN

 

[pbornemeier]

I don't believe you have a field named "Sum of FCR"

Perhaps you wanted to do something like:

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("FCR"), "Max of FCR", xlMax

Please use code tags to make your posts easier to read. See link in my sig.