Confused on VBA copy destination

Question

I'm trying to make a chart using chosen data from one sheet to create a totally new sheet that displays the chart. I'm extremely new to VBA.

My current code is:

Dim rng As Range
Set rng = Selection

    ' add a chart and select it - Selection becomes this chart
    ActiveSheet.Shapes.AddChart.Select
    
    'paste selection into cell C4 of Sheet2
    rng.Copy Destination:=Sheets("Sheet2").Range("C4")
    
    Sheets("Sheet2").Activate
   
    With ActiveChart
        'Chart type is Clustered Bar chart
        .ChartType = xlBarClustered
      
        'Set a chart title, located at the top of the chart
        .SetElement msoElementChartTitleAboveChart
      
        'Assign the content of cell B1 to the title of the chart
        .chartTitle.Text = Worksheets("Sheet1").Range("B1").Value
      
        'Move the chart to a new sheet 
        .Location Where:=xlLocationAsNewSheet, Name:="Sheet2"
    End With

It keeps showing a run-time error 13 with the Set rng = Selection and then sometimes an out-of-range error pops up around the copy. destination.

My hypothesis is that the out-of-range problem appears because "Sheet2" is not yet a sheet, however, when I create Sheet2, a method SetElement of Chart object failed error appears. It doesn't appear to make a difference whether the Copy Destination lines are within or outside of the ActiveChart.

Answer 1

Try this:

Sub so75496418AddChart()

Dim rng As Range
Dim strTypeName As String, strNewSheetName As String

  strTypeName = TypeName(Selection)

  If strTypeName = "Range" Then

    Set rng = Selection

    If WorksheetFunction.CountA(rng) = 0 Then
      MsgBox "No data in range."
      Set rng = Nothing
      Exit Sub
    End If

    'paste selection into cell C4 of Sheet2
    'rng.Copy Destination:=Sheets("Sheet2").Range("C4")

    strNewSheetName = "Sheet" & (Sheets.Count + 1)

    With ActiveSheet.Shapes.AddChart.Chart
        'Chart type is Clustered Bar chart
        .ChartType = xlBarClustered

        .SetSourceData Source:=rng

        'Set a chart title, located at the top of the chart
        .SetElement msoElementChartTitleAboveChart

        'Assign the content of cell B1 to the title of the chart
        .ChartTitle.Text = ActiveSheet.Range("B1").Value

        'Move the chart to a new sheet
        .Location Where:=xlLocationAsNewSheet, Name:=strNewSheetName

    End With

    ' move the sheet as the last sheet:
    Sheets(strNewSheetName).Move after:=Sheets(Sheets.Count)

    Set rng = Nothing

  Else
    MsgBox "Current selection is not a range, but a " & strTypeName & "."
  End If

End Sub