I’ve made a macro so that I can select two data columns to create a chart. I want the chart title to include the data column title (row 1) from the first column I have selected, but as I use the macro on multiple data columns within a sheet, it needs to automatically update the title from the appropriate (selected) column of data.
Sub Graphs() Dim my_range As Range Set my_range = Union(Selection, ActiveSheet.Range("A:A")) ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered ActiveChart.FullSeriesCollection(1).AxisGroup = 1 ActiveChart.FullSeriesCollection(2).ChartType = xlLine ActiveChart.FullSeriesCollection(2).AxisGroup = 1 ActiveChart.FullSeriesCollection(1).ChartType = xlXYScatter ActiveChart.FullSeriesCollection(1).AxisGroup = 1 ActiveChart.SetSourceData Source:=my_range ActiveChart.HasTitle = True ActiveChart.ChartTitle.Text = Cells(1, 1).Value " - " ActiveSheet.Name ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs" End Sub
I have managed to make the title named from a specific cell (1, 6 in this example):
ActiveChart.ChartTitle.Text = Cells(1, 6).Value " - " ActiveSheet.Name
But I want it to change to the cell from the column of data I have selected