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") .Select .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