(Solved) – Unable to move Boxplot chart successfully

  • by

I have been looking around the web and have since gathered some codes that will create a box plot and move it to another worksheet. However, the following code is able to create the box plot but unable to move it to a new sheet and when it tried, this error pops up

Run-time error ‘1004’ Method ‘Location’ of object ‘_Chart’ failed

on this line

Set c = c.Location(where:=xlLocationAsNewSheet, Name:="newChartSheetName")

and when i tried to run it again, my excel file will just close itself without saving. I have tried to create a boxplot at the worksheet i wanted it to be but the data is from another worksheet which makes the chart empty which is why i changed to this method. The following is my current attempt to this problem. Appreciate any help given.

Sub test_boxplot()
    Dim chart_title As String
    Dim RngToCover As Range
    Dim ChtOb As ChartObject, c As Chart

    With Sheets("Data")
        .Range("E6:E425").Select ' I understand this is not an efficient way to go about it.

        chart_title = .Range("E2")

        .Shapes.AddChart2(408, xlBoxwhisker).Select
        ActiveChart.ChartTitle.Text = chart_title
    End With

    With Sheets("Graphs")
        Set RngToCover = .Range("L6:O23")      
    End With

    Set ChtOb = ActiveChart.Parent
    Set c = ChtOb.Chart

    Set c = c.Location(where:=xlLocationAsNewSheet, Name:="newChartSheetName") 'Error here    
    c.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)         

    With ChtOb
        .Height = RngToCover.Height ' resize
        .Width = RngToCover.Width   ' resize
        .Top = RngToCover.Top       ' reposition
        .Left = RngToCover.Left     ' reposition
    End With

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *