Solved: Auto Plot Chart routine fails

[JimS]

Below is some VBA that automatically creates a Chart for every sheet name that is on the Worksheet labeled CList (starting with Cell A4 and continuing down Column A for as many sheet names that are in the list.

This code works perfectly for one exception.

It fails with a “Run-time Error 9 / Subscript out of Range” error if there is only 1 sheet name on the CList (data only in Cell A4 and nothing below it) at this line:

Set WS = ThisWorkbook.Worksheets(NameCell.Value)


Any ideas as to why this is happening?

Thanks for any and all help…

JimS


Sub PlotCharts()
Dim NameRange As Range, NameCell As Range
Dim ChartRange As Range, BaseRange As Range, i As Long
Dim CH As Chart
Dim WS As Worksheet

Application.ScreenUpdating = False

Set NameRange = ThisWorkbook.Worksheets("CList").Range("A4")
Set NameRange = Range(NameRange, NameRange.End(xlDown))
For Each NameCell In NameRange.Cells
Set WS = ThisWorkbook.Worksheets(NameCell.Value)
i = 1
Set BaseRange = WS.Range("A5", WS.Range("A" & WS.Rows.Count).End(xlUp))
Set ChartRange = BaseRange
Do
If WS.Range("A5").Offset(, i * 6 - 4).Value = "" Then Exit Do
Set ChartRange = Union(ChartRange, BaseRange.Offset(, i * 6 - 4).Resize(, 2))
i = i + 1
Loop While i < 43
Set CH = ThisWorkbook.Charts.Add
With CH
.ChartType = xlColumnClustered
.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet, Name:=WS.Name & " Chart"
.PlotArea.Interior.ColorIndex = 2
.PlotArea.Width = CH.ChartArea.Width - 15
With .Legend
.Top = 10
.Left = CH.Axes(xlValue).Left + 10
.Height = (i - 1) * 24
.Width = 300
.Shadow = False
.Interior.ColorIndex = xlNone
.Border.LineStyle = x1None

End With
End With

ActiveChart.Deselect

Next

Application.ScreenUpdating = True

End Sub

[SamT]

With only one name


Set NameRange = Range(NameRange, NameRange.End(xlDown))

will make NameRange = A4:A65536

[JimS]

Thanks for looking at this but I'm not following what you're getting at?

I was thinking that the issue is that the routine is using a range but it's only a range of 1 when there is only single entry on the CList sheet.

Will that make a difference?

[SamT]

For Each NameCell In NameRange.Cells
Set WS = ThisWorkbook.Worksheets(NameCell.Value)

There are 65K plus NameCells. Only one has a value. The second time thru, you're trying to Set Ws to Null.

Try this

yaddah, yaddah
If NameRange.Cells.Count > 65000 Then Set NameRange = Range("A4")
For Each NameCell In NameRange.Cells
Set WS = ThisWorkbook.Worksheets(NameCell.Value)
yaddah, yaddah

[JimS]

Same error... Subscript out of Range on the "Set WS" line


Set NameRange = ThisWorkbook.Worksheets("CList").Range("A4")
Set NameRange = Range(NameRange, NameRange.End(xlDown))
'For Each NameCell In NameRange.Cells

If NameRange.Cells.Count > 65000 Then Set NameRange = Range("A4")
For Each NameCell In NameRange.Cells

Set WS = ThisWorkbook.Worksheets(NameCell.Value)

[JimS]

Got it - it needed to be this:

If NameRange.Cells.Count > 65000 Then Set NameRange = ThisWorkbook.Worksheets("CList").Range("A4")

Thanks for your help...