0

I currently have a workbook and a series of codes that create a PivotTable from the data in the workbook.

One of my codes copies the completed PivotTable and pasted it from the relevant Worksheet to another Worksheet, sorts the data, removes the top row (which is the grand total) and then the data from the PivotTable is pasted into a table on the Worksheet.

The PivotTable itself is two columns, headed "Row Label" and "Count of Calling Number".

My code has worked fine until now. I current get the following error:

"Run Time error 1004 unable to get pivot items property of the pivot field class" with the line ".PivotItems ("").Visible = False highlighted yellow.

Any ideas why I am getting this error?


Sheets("Pivottable").Select
    Range("A2").Select
    With ActiveSheet.PivotTables("PRIMEPivotTable").PivotFields("Calling Number")
        .PivotItems("").Visible = False
    End With
    Columns("A:B").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("N1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("N:O").EntireColumn.AutoFit
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add2 Key:=Range( _
        "O2:O100000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("N1:O100000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("N2:O2").Select
    Selection.Copy
    Range("F1").Select
    ActiveSheet.Paste
    Range("N2:O2").Select
    Selection.Delete Shift:=xlUp
    Range("N2:N21").Select
    Selection.Copy
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O2:O21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/R1C[1])"
    Selection.AutoFill Destination:=Range("F4:F23")
    Range("F4:F23").Select

End Sub
Chris Ellis
  • 39
  • 6
  • At least 1 PivotItem must remain visible, otherwise you get an error. If you want to hide blank entries, use `PivotItems("(blank)").Visible = False` (or whatever is shown there in your language). You should also [avoid selecting or activating anything](https://stackoverflow.com/q/10714251/10908769). – Asger Apr 25 '19 at 13:53
  • Hi @Asger I am not quite sure what you mean. How would I go about ensuring that at least 1 item remains visible? – Chris Ellis Apr 25 '19 at 13:58
  • In case your pivotfield "Calling Number" only has empty entries, then you can not hide all of them, as at least 1 always has to remain visible. Look [here](https://stackoverflow.com/a/54420639/10908769) concerning hiding blank entries. – Asger Apr 25 '19 at 14:21

0 Answers0