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
'paste selection into cell C4 of Sheet2
'Chart type is Clustered Bar chart
.ChartType = xlBarClustered
'Set a chart title, located at the top of the chart
'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"
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.