Hi,

I'm new to vba and have been struggling with this problem for a while.

I want to create a simple sub to create several column charts in a sheet in the current workbook.

I have created named ranges for the rows data I want to represent in the series. One name = one row of data. The names are in the workbook scope. Therefore they should be globally available.

I have verified the named ranges are correct (I used the Find&Select ~> GoTo menu. It shows all my named ranges and when selected they highlight the right data.

The subroutine is creating the plot, changing the title but I can not get it to use the named ranges for the data so I have to have some silly error there.

I have added the named range interactively to the plot itself and it worked. I tried using that syntax in the VBA sub and it did not work.

I have tried using the .Range and .Values and have not been able to get it to work. Even hard coding the named range just to test it (as seen below).

' series_labels is an array of strings that indicate what is the series presented (i.e "Section A", "Section B")
' series_names this is an array of the Excel range names that represent the data that will be used for the series.
' x_axis_labels is a list of strings (i.e "Monday", "Tuesday") that contains the labels that will be set in the X-axis identifying the groups of columns

Sub CreateColChart(sh As Worksheet, title As String, target_location As Range, ByRef series_labels() As String, ByRef series_names() As String, ByRef x_axis_labels() As String)
Dim BChart As Chart
Dim I As Long

' Create the column chart
Set BChart = sh.Shapes.AddChart(xlColumnClustered).Chart

With BChart
.HasTitle = True
.ChartTitle.Text = title

MsgBox ("Workbook " & vbNewLine & ThisWorkbook.Path)

For I = LBound(series_labels) To UBound(series_labels)

' Defines a new series to the chart
.SeriesCollection.NewSeries
With .SeriesCollection(I)

.Range = Range("=" & ThisWorkbook.Name & "!bulk_util")
'.Values = "=" & ThisWorkbook.Name & "!bulk_util"
.Name = series_labels(I)
End With
Next I
End With

End Sub


Thanks in advance.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)