0
Sub Test()
'
' Test Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Range("A1:N226").Select
    Sheets.Add
This is the problem area -----
*    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "reports_statistics 20230301!R1C1:R226C14", Version:=8).CreatePivotTable _
        TableDestination:="NewSheet!R1C1", TableName:="PivotTable2", DefaultVersion _
        :=8*
End of problem area -------
    Sheets("Sheet6").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$C$18")
    ActiveSheet.Shapes("Chart 1").IncrementLeft 192
    ActiveSheet.Shapes("Chart 1").IncrementTop 15
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Operator Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("RejectEventCt"), "Sum of RejectEventCt", xlSum
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("ReportTime(Holter)"), "Sum of ReportTime(Holter)", _
        xlSum
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("ConfEventCt"), "Sum of ConfEventCt", xlSum
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("Summary"), "Sum of Summary", xlSum
    ActiveSheet.Shapes("Chart 1").IncrementLeft 131.25
    ActiveSheet.Shapes("Chart 1").IncrementTop -3
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Summary")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of RejectEventCt")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Sum of ReportTime(Holter)")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("ReportTime(Holter)"), "Sum of ReportTime(Holter)", _
        xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ConfEventCt")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables(-1).PivotFields("Summary").CurrentPage = "(All)"
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Summary")
        .PivotItems("1").Visible = False
        .PivotItems("2").Visible = False
        .PivotItems("3").Visible = False
        .PivotItems("4").Visible = False
        .PivotItems("5").Visible = False
        .PivotItems("6").Visible = False
        .PivotItems("7").Visible = False
        .PivotItems("8").Visible = False
        .PivotItems("9").Visible = False
        .PivotItems("10").Visible = False
        .PivotItems("11").Visible = False
        .PivotItems("12").Visible = False
        .PivotItems("13").Visible = False
        .PivotItems("14").Visible = False
        .PivotItems("15").Visible = False
        .PivotItems("16").Visible = False
        .PivotItems("17").Visible = False
        .PivotItems("18").Visible = False
        .PivotItems("19").Visible = False
        .PivotItems("20").Visible = False
        .PivotItems("21").Visible = False
        .PivotItems("22").Visible = False
        .PivotItems("23").Visible = False
        .PivotItems("24").Visible = False
        .PivotItems("27").Visible = False
        .PivotItems("28").Visible = False
        .PivotItems("29").Visible = False
        .PivotItems("30").Visible = False
        .PivotItems("31").Visible = False
        .PivotItems("34").Visible = False
        .PivotItems("42").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Summary"). _
        EnableMultiplePageItems = True
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.PivotTables(-1).PivotFields("RejectEventCt").CurrentPage = "(All)"
    With ActiveChart.PivotLayout.PivotTable.PivotFields("RejectEventCt")
        .PivotItems("208").Visible = False
        .PivotItems("209").Visible = False
        .PivotItems("216").Visible = False
        .PivotItems("219").Visible = False
        .PivotItems("222").Visible = False
        .PivotItems("235").Visible = False
        .PivotItems("236").Visible = False
        .PivotItems("241").Visible = False
        .PivotItems("244").Visible = False
        .PivotItems("255").Visible = False
        .PivotItems("257").Visible = False
        .PivotItems("270").Visible = False
        .PivotItems("271").Visible = False
        .PivotItems("280").Visible = False
        .PivotItems("281").Visible = False
        .PivotItems("284").Visible = False
        .PivotItems("290").Visible = False
        .PivotItems("295").Visible = False
        .PivotItems("298").Visible = False
        .PivotItems("303").Visible = False
        .PivotItems("304").Visible = False
        .PivotItems("305").Visible = False
        .PivotItems("309").Visible = False
        .PivotItems("316").Visible = False
        .PivotItems("317").Visible = False
        .PivotItems("320").Visible = False
        .PivotItems("323").Visible = False
        .PivotItems("332").Visible = False
        .PivotItems("334").Visible = False
        .PivotItems("340").Visible = False
        .PivotItems("343").Visible = False
        .PivotItems("350").Visible = False
        .PivotItems("351").Visible = False
        .PivotItems("352").Visible = False
        .PivotItems("357").Visible = False
        .PivotItems("359").Visible = False
        .PivotItems("368").Visible = False
        .PivotItems("371").Visible = False
        .PivotItems("379").Visible = False
        .PivotItems("380").Visible = False
        .PivotItems("388").Visible = False
        .PivotItems("394").Visible = False
        .PivotItems("396").Visible = False
        .PivotItems("401").Visible = False
        .PivotItems("403").Visible = False
        .PivotItems("410").Visible = False
        .PivotItems("424").Visible = False
        .PivotItems("425").Visible = False
        .PivotItems("430").Visible = False
        .PivotItems("435").Visible = False
        .PivotItems("469").Visible = False
        .PivotItems("473").Visible = False
        .PivotItems("492").Visible = False
        .PivotItems("495").Visible = False
    End With
    With ActiveChart.PivotLayout.PivotTable.PivotFields("RejectEventCt")
        .PivotItems("504").Visible = False
        .PivotItems("508").Visible = False
        .PivotItems("511").Visible = False
        .PivotItems("523").Visible = False
        .PivotItems("531").Visible = False
        .PivotItems("545").Visible = False
        .PivotItems("560").Visible = False
        .PivotItems("565").Visible = False
        .PivotItems("579").Visible = False
        .PivotItems("592").Visible = False
        .PivotItems("594").Visible = False
        .PivotItems("624").Visible = False
        .PivotItems("638").Visible = False
        .PivotItems("647").Visible = False
        .PivotItems("648").Visible = False
        .PivotItems("649").Visible = False
        .PivotItems("669").Visible = False
        .PivotItems("678").Visible = False
        .PivotItems("687").Visible = False
        .PivotItems("727").Visible = False
        .PivotItems("786").Visible = False
        .PivotItems("832").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("RejectEventCt"). _
        EnableMultiplePageItems = True
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.PivotTables(-1).PivotFields("ConfEventCt").CurrentPage = "(All)"
    With ActiveChart.PivotLayout.PivotTable.PivotFields("ConfEventCt")
        .PivotItems("219").Visible = False
        .PivotItems("220").Visible = False
        .PivotItems("237").Visible = False
        .PivotItems("241").Visible = False
        .PivotItems("243").Visible = False
        .PivotItems("259").Visible = False
        .PivotItems("277").Visible = False
        .PivotItems("279").Visible = False
        .PivotItems("280").Visible = False
        .PivotItems("289").Visible = False
        .PivotItems("290").Visible = False
        .PivotItems("294").Visible = False
        .PivotItems("295").Visible = False
        .PivotItems("300").Visible = False
        .PivotItems("303").Visible = False
        .PivotItems("305").Visible = False
        .PivotItems("322").Visible = False
        .PivotItems("329").Visible = False
        .PivotItems("330").Visible = False
        .PivotItems("335").Visible = False
        .PivotItems("336").Visible = False
        .PivotItems("341").Visible = False
        .PivotItems("345").Visible = False
        .PivotItems("354").Visible = False
        .PivotItems("358").Visible = False
        .PivotItems("367").Visible = False
        .PivotItems("380").Visible = False
        .PivotItems("386").Visible = False
        .PivotItems("390").Visible = False
        .PivotItems("391").Visible = False
        .PivotItems("395").Visible = False
        .PivotItems("402").Visible = False
        .PivotItems("428").Visible = False
        .PivotItems("453").Visible = False
        .PivotItems("454").Visible = False
        .PivotItems("458").Visible = False
        .PivotItems("480").Visible = False
        .PivotItems("494").Visible = False
        .PivotItems("518").Visible = False
        .PivotItems("550").Visible = False
        .PivotItems("553").Visible = False
        .PivotItems("556").Visible = False
        .PivotItems("578").Visible = False
        .PivotItems("599").Visible = False
        .PivotItems("607").Visible = False
        .PivotItems("639").Visible = False
        .PivotItems("645").Visible = False
        .PivotItems("652").Visible = False
        .PivotItems("658").Visible = False
        .PivotItems("688").Visible = False
        .PivotItems("721").Visible = False
        .PivotItems("727").Visible = False
        .PivotItems("737").Visible = False
        .PivotItems("741").Visible = False
    End With
    With ActiveChart.PivotLayout.PivotTable.PivotFields("ConfEventCt")
        .PivotItems("788").Visible = False
        .PivotItems("789").Visible = False
        .PivotItems("793").Visible = False
        .PivotItems("799").Visible = False
        .PivotItems("821").Visible = False
        .PivotItems("827").Visible = False
        .PivotItems("837").Visible = False
        .PivotItems("854").Visible = False
        .PivotItems("860").Visible = False
        .PivotItems("961").Visible = False
        .PivotItems("962").Visible = False
        .PivotItems("982").Visible = False
        .PivotItems("1068").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("ConfEventCt"). _
        EnableMultiplePageItems = True
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.Shapes("Chart 1").IncrementLeft -363
    ActiveSheet.Shapes("Chart 1").IncrementTop -12
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.3257575758, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0798611111, msoFalse, _
        msoScaleFromTopLeft
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Operator Name")
        .PivotItems("Angie, Kalamaris").Visible = False
        .PivotItems("Azam, Okilov").Visible = False
        .PivotItems("jgooch").Visible = False
        .PivotItems("John Chatto").Visible = False
        .PivotItems("Kata, Villegas").Visible = False
        .PivotItems("Kimico, Grant").Visible = False
        .PivotItems("Kristi, Robinson").Visible = False
        .PivotItems("patrika patel").Visible = False
        .PivotItems("Ruzica").Visible = False
    End With
    ActiveSheet.ChartObjects("Chart 1").Activate
    Range("B5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ReportTime(Holter)") _
        .Function = xlAverage
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.Shapes("Chart 1").IncrementLeft 64.5
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), _
        "Operator Name").Slicers.Add ActiveSheet, , "Operator Name", "Operator Name", _
        165.75, 508.5, 144, 198.75
    ActiveSheet.Shapes.Range(Array("Operator Name")).Select
    ActiveSheet.Shapes("Operator Name").IncrementLeft -195.75
    ActiveSheet.Shapes("Operator Name").IncrementTop 69
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), "Day" _
        , , xlTimeline).Slicers.Add ActiveSheet, , "Day", "Day", 215.25, 449.25, 262.5 _
        , 108
    ActiveSheet.Shapes.Range(Array("Day")).Select
    ActiveSheet.Shapes("Day").IncrementLeft 38.25
    ActiveSheet.Shapes("Day").IncrementTop 21.75
    ActiveWorkbook.SlicerCaches("NativeTimeline_Day").Slicers("Day"). _
        TimelineViewState.Level = xlTimelineLevelDays
    ActiveWorkbook.SlicerCaches("NativeTimeline_Day").TimelineState. _
        SetFilterDateRange "2/1/2023", "2/1/2023"
    ActiveWorkbook.SlicerCaches("NativeTimeline_Day").TimelineState. _
        SetFilterDateRange "2/1/2023", "2/6/2023"
    Range("H24").Select
End Sub

I tried recording a macro to automate data from a spreadsheet into a specific pivotchart/table. When I run the macro, an error 1004 comes up and the debugger highlights the area of code near the top. I have no Idea what is incorrect about that code

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
timmx240
  • 1
  • You've got to tell us which line it's having an error on. – SandPiper Mar 01 '23 at 16:46
  • Do you have a sheet called `reports_statistics 20230301` with data in range `A1:N226` ? Probably error is because new sheet is not called `NewSheet`. Change to `TableDestination:=ActiveSheet.Name & "!R1C1` etc – CDP1802 Mar 01 '23 at 17:48

0 Answers0