Graph Creation Problems

Hi,
I was hoping someone with more experience than me could figure out why the highlighted code causes an error in the macro. This is only a snippet in the code but I think the only part that is important. Basically the procedure sets up an auto updating graph that accepts variables for : the worksheet it is embedded on, the worksheet the source data is on , the columns from which to use ranges from and the ActiveWorkbook.Names used. It is the ActiveWorkbook.Names that cause the problem. when I try to concatenate the name with the sheet name I get an error on the 'series' line of code (hilighted) if I omit the sheet name the error is fixed, but then all the graphs point to the same set of data. I need a way of automating the creation of a unique name. It seems that the best way would be to add the sheet name. If anyone can help me with this issue I would be really greatful. Thanks

Sub graphMaker(columnValueOne, columnValueTwo, nameInputOne, nameInputTwo)
Dim varOne As Range
Dim varTwo As Range
Dim nameOne As String
Dim nameTwo As String
Set varOne = Cells(4, columnValueOne)
Set varTwo = Cells(4, columnValueTwo)
Set SourceSheetName = Sheets(GeneratedPageName) 'ActiveSheet 

'   ***************** THIS IS POINT THAT GENERATES ERROR

nameOne = nameInputOne & "_" & SourceSheetName.name
nameTwo = nameInputTwo & "_" & SourceSheetName.name

'   **********************************************
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets(SourceSheetName.name).Range(varOne.Address & ":" & varTwo.Address)
ActiveChart.Location Where:=xlLocationAsObject, name:=NewSheetName
ActiveWorkbook.Names.Add name:=nameOne, RefersToR1C1:= _
"=OFFSET(" & SourceSheetName.name & "!R4C" & columnValueOne & ",0,0,COUNTA(" & SourceSheetName.name & "!C" & columnValueOne & ")-1)"
ActiveWorkbook.Names.Add name:=nameTwo, RefersToR1C1:= _
"=OFFSET(" & SourceSheetName.name & "!R4C" & columnValueTwo & ",0,0,COUNTA(" & SourceSheetName.name & "!C" & columnValueTwo & ")-1)"
ActiveChart.SeriesCollection(1).Formula = _

'   Error occurs here  ************************************

"=SERIES(," & SourceSheetName.name & "!" & nameOne & "," & SourceSheetName.name & "!" & nameTwo & ",1)"

You will probably need to post an example, but at a glance:

Set SourceSheetName = Sheets(GeneratedPageName)

1. Where is GeneratedPageName initialized?

2. SourceSheetName.name will have to be enclosed in single quotes if it contains spaces.

3. In the Sub's declarations, the argument types should be declared.

Thank you very much for the speedy reply

1) GeneratedPageName is initialized at the top of the module (outside of any procedures) . I don't know if this is relevant but the its value is a number. Having said that, SourceSheetName.name is used multiple times in the code without causing any problems

Set SourceSheetName = Sheets(GeneratedPageName)

2) it contains no spaces

3) when you say they must be declared, do you mean within the Sub?

again thanks for the help

The Sub declaration should look something like this:

Sub graphMaker(columnValueOne As Long, columnValueTwo As Long, nameInputOne As String, nameInputTwo As String)

Can you post an example that illustrates the problem?

I am not sure what you mean. Do you want the xls file?

Yes, please.

Sorry for the delay, and thanks for your previous help Shg. I tried my best to strip away any code that was unnecessary and unrelated. Then I realized you can't upload .xls files so I started stripping it down even further so i could send it as a word document that would work with the smallest input possible. In the process I stumbled on the problem. It was one of the variables. Thanks for the advice about declaring all variables etc. that definitely was part of the solution. But alas I am now stuck at a new hurdle though i feel it is much simpler.

The Sub makeGraph now works properly

[CODE]Public Sub graphMaker(columnValueOne As Integer, columnValueTwo As Integer, nameinputOne As String, nameinputTwo As String)
GraphCycleFinished = False
Dim varOne As Range
Dim varTwo As Range
Dim nameOne As String
Dim nameTwo As String
Dim SourceSheet As Object
Dim SourceSheetName As String
Set SourceSheet = Sheets(GeneratedPageName) 'ActiveSheet '*****************
SourceSheetName = SourceSheet.name
MsgBox SourceSheetName
Set varOne = SourceSheet.Cells(4, columnValueOne)
Set varTwo = SourceSheet.Cells(4, columnValueTwo)
nameOne = nameinputOne & SourceSheetName
nameTwo = nameinputTwo & SourceSheetName
MsgBox nameOne
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets(GeneratedPageName).Range(varOne.Address & ":" & varOne.Address & "," & varTwo.Address & ":" & varTwo.Address)
ActiveChart.Location Where:=xlLocationAsObject, name:=NewSheetName
ActiveWorkbook.Names.Add name:=nameOne, RefersToR1C1:= _
"=OFFSET(" & Sheets(GeneratedPageName).name & "!R4C" & columnValueOne & ",0,0,COUNTA(" & Sheets(GeneratedPageName).name & "!C" & columnValueOne & ")-1)"
ActiveWorkbook.Names.Add name:=nameTwo, RefersToR1C1:= _
"=OFFSET(" & Sheets(GeneratedPageName).name & "!R4C" & columnValueTwo & ",0,0,COUNTA(" & Sheets(GeneratedPageName).name & "!C" & columnValueTwo & ")-1)"
ActiveChart.SeriesCollection(1).Formula = _
"=SERIES(," & Sheets(GeneratedPageName).name & "!" & nameOne & "," & Sheets(GeneratedPageName).name & "!" & nameTwo & ",1)"
With ActiveChart.Parent
.Width = widthAmount
.Height = heightAmount
.Left = 30 + RowChartNumber * (widthAmount + seperatorColumn)
.Top = 30 + ColumnChartNumber * (heightAmount + seperatorColumn)
End With
If RowChartNumber / 2 = 1 Then
RowChartNumber = 1
ColumnChartNumber = ColumnChartNumber + 1
End If
RowChartNumber = RowChartNumber + 1
End Sub
/CODE]

it is called in another sup passing certain arguments namely the column numbers and part of a unique name to be added using

ActiveWorkbook.Names.Add name:=nameOne, RefersToR1C1:= _ ' ..... etc.

The sub is called w/ arguments sucha s this

Call graphMaker(6, 7, "a", "b")

the problem arises when i try to call it a second time right after the first, so as to add a new graph

Call graphMaker(2, 1, "c", "d")

I get :

Run-time error "1004"
that name is not valid

refering to the activeworkbook line that worked the first time round. The name has only changed by one letter. i don't get it.

Any insight as to wat is going on

Thanks