Referencing Variable Worksheet names in chart source data

[lankybones]

Forgive me if I am wrong but I cannot find the answer to my question on other threads so I am going to ask.

Background Info:
Using - Excel 2007 through windows xp
I am accessing a SQL server to retrieve a table of data. I am then using a macro to open a userform in which the user inputs data to filter the table by. When they hit the command button after inputting said data the macro filters the columns by the data input from the user then creates 3 charts from the data after filtering.

The Problem:
The user may want to run this macro more than once per workbook so the worksheets created I have running as a string and variable that counts the number of times the macro runs through a public variable, that puts a number after the string (ex. on first run of macro "sheet 1" is created, on second run of macro "sheet 2" is made). My problem is referencing these variable names in the seriescollection in the creation of the charts. My charts show up blank (no data points).

Code:
intGraphButt*******s - variable for number of times graph button was hit
"Neg.Commit Data " & intGraphButt*******s - variable sheet name

Sheets.Add.Name = "Neg.Commit Data " & intGraphButt*******s (no problems with sheet name being correct)

Charts("Negotiated Commit Dates " & intGraphButt*******s).SeriesCollection(1).XValues = "='Neg.Commit Data & intGraphButt*******s'!$A$2:$A$32000"
(within this line of code is where the problem lays)

Respond with any questions or clarifications i need to make Thanks for your time!

 

[lankybones]

If it helps any I know the sheets created by my program are working as expected the only problem is within referencing the sheet so it should be a simple fix for someone out there with more knowledge than me but i just cant get the right syntax.

 

[lankybones]

Ok so another reply to myself sorry for making this repetitive but i think i found a useful piece of information. when i right click on the graph and click select data it says my data range is too complex. I dont know why this is because its just a simple column from another worksheet within the workbook? does this help?

 

[xenou]

Charts("Negotiated Commit Dates " & intGraphButt*******s).SeriesCollection(1).XValues = "='Neg.Commit Data & intGraphButt*******s'!$A$2:$A$32000"

The concatenation looks wrong. Should be:

Charts("Negotiated Commit Dates " & intGraphButt*******s).SeriesCollection(1).XValues = "='Neg.Commit Data" & intGraphButt*******s & "'!$A$2:$A$32000"

Out of habit I would probably go in steps (beware that I might have missed spaces in your sheet names or range addresses - check the exact outcome in strFormula):

Dim ws As Worksheet
Dim strFormula As String
Set ws = Sheets.Add
ws.Name = "Neg.Commit Data " & X
strFormula = "='Neg.Commit Data" & X & "'!$A$2:$A$32000"
Debug.Print strFormula
Charts("Negotiated Commit Dates " & X).SeriesCollection(1).XValues = strFormula

 

[lankybones]

Worked exactly like I was hoping for thank you so much. Sucks how much simple punctuation can influence a program but i guess thats part of the fun haha. Thanks again for your time and God bless!!!