trying to create a code that takes any set of data, no matter how long and wide, and creates individual charts for each column of data. Currently my .Name = ActiveSheet.Name wont capitalise the name and is bugging out.
ideally want to get the code to a state where it doesnt matter what sheet the data is in and where the data starts within the sheet.
full code below, very open to improvements and very much appreciated!
Sub AddCharts3()
Dim i As Integer 'rows
Dim j As Integer 'columns
Dim Col As Integer
i = Cells(Rows.count, 2).End(xlUp).Row
Col = Cells(5, Columns.count).End(xlToLeft).Column
For j = 3 To Col
With Sheets("CMT Data").Shapes.AddChart.Chart
.ChartType = xlColumnClustered
.SeriesCollection.NewSeries
With .SeriesCollection(1)
.name = "=" & ActiveSheet.name & "!" & _
Cells(5, j).Address
.XValues = "=" & ActiveSheet.name & "!" & _
Range(Cells(6, 2), Cells(i, 2)).Address
.Values = "=" & ActiveSheet.name & "!" & _
Range(Cells(6, j), Cells(i, j)).Address
End With
.Location xlLocationAsObject, name:="ChartPack"
.HasLegend = False
Sheets("Data").Select
End With
Next j
Dim shp As Shape
Dim Cnt As Long
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim start As Double
Const ColCnt As Long = 5
Cnt = 1
Sheets("ChartPack").Select
ActiveSheet.ChartObjects.Select
For Each shp In Selection.ShapeRange
With shp
If Cnt = 1 Then
.Top = Range("B5").Top
.Left = Range("B5").Left
start = .Left
Else
If Cnt Mod ColCnt = 1 Then
.Top = dTop + dHeight
.Left = start
Else
.Top = dTop
.Left = dLeft + dWidth
End If
End If
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
End With
Cnt = Cnt + 1
Next shp
End Sub