Chart Values and X axis values from VBA

[Kohai]

Hi,

I'm creating a series of charts based on data in many
adjacent columns based on Name defined Ranges. However,
I have to hardcode the Workbook and Sheet names that are
used for the source names. My piece of code is:

With ChtObj.Chart.SeriesCollection(jbl)
.Values = "=Book3.xls!Sheet1_COL_" & ColAry(z)
.XValues = "=Book3.xls!Sheet1_Date"
End With

I want the code to handle the workbook & sheet names more
dynamically to accept a variable for wbk name or sheet
name (or number). I can't get any syntax to work.
Experts Help!

Thank you.

Kohai

 

[Jon Peltier]

Try this:

.Values = "=" & sBookName & "!" & sSheetName

or

.Values = "=" & sBookName & "!" & sRangeName

or better yet:

.Values = activesheet.range(sRangeName)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Kohai]

Jon

Thanks for that. I had checked your web site trying to
find an answer. The dilemma I have now is that the
active workbook name contains spaces, so I need to
incorporate the single quote marks. Those however are
for comments in VBA, so I'm not sure how to get around
that.

 

[Jon Peltier]

Keep the single quotes inside the double quotes, and Excel will not be
confused.

.Values = "='[" & sBookName & "]" & sSheetName & "'!" & sRangeName
.Values = "='" & sBookName & "'!" & sRangeName

I was wrong in the first line I provided. It should have been

.Values = "=[" & sBookName & "]" & sSheetName & "!" & sRangeName

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______