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

[vba]
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
[/vba]

[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]

[vba]
For Each NameCell In NameRange.Cells
Set WS = ThisWorkbook.Worksheets(NameCell.Value)
[/vba]
There are 65K plus NameCells. Only one has a value. The second time thru, you're trying to Set Ws to Null.

Try this
[vba]
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
[/vba]

[JimS]

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

[VBA]
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)
[/VBA]

[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...