suresh ullanki
Board Regular
- Joined
- Apr 29, 2013
- Messages
- 67
Hi,
The following VBA code creates charts. however, It is not picking up correct legend which should be Range("$J$1:$S$1") for all the charts. it is picking up as Seres1, series2 and etc... please help to correct the code
Sub test()
Dim Row As Integer
Dim ws As Worksheet
Dim rng As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set ws = Sheets("Sheet1")
For Row = 1 To lastrow
Set rng = ws.Range("$J$1:$S$1").Offset(Row, 0)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(ws.Name & "!" & rng.Address)
ActiveChart.ChartType = xlBarClustered
ActiveChart.PlotBy = xlColumns
ActiveChart.SetElement (msoElementLegendBottom)
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 222
ActiveChart.ChartTitle.Text = "WIP"
ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
Next Row
Set ws = Nothing
Set rng = Nothing
End Sub
The following VBA code creates charts. however, It is not picking up correct legend which should be Range("$J$1:$S$1") for all the charts. it is picking up as Seres1, series2 and etc... please help to correct the code
Sub test()
Dim Row As Integer
Dim ws As Worksheet
Dim rng As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set ws = Sheets("Sheet1")
For Row = 1 To lastrow
Set rng = ws.Range("$J$1:$S$1").Offset(Row, 0)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(ws.Name & "!" & rng.Address)
ActiveChart.ChartType = xlBarClustered
ActiveChart.PlotBy = xlColumns
ActiveChart.SetElement (msoElementLegendBottom)
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 222
ActiveChart.ChartTitle.Text = "WIP"
ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
Next Row
Set ws = Nothing
Set rng = Nothing
End Sub