Naming a worksheet as a variable in a Active Chart Series

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Farooq Sheri - 24 Jan 2006 10:42 GMT

I have about 100 sheets each named in terms of a node in a telecom network.
For each node I have to graph a series of values and also find a max within a
range. I want to use a variable naming convention for worsheets in my VB
code. For example in the following line of code instead of the sheet name I
would like to use a variable such as str:
dim str as string
Sheets("CCA01(7011)").Activate
str = ActiveSheet.Name
ActiveChart.SeriesCollection(1).XValues = "='CCA01(7011)'!R2C4:R14C4".

Instead writing this line for each sheet I want to use a loop to each sheet
and for each sheet I want to execute the above code. Let us say my sheets are
named MSC01, MSC02, MSC03,...

Thanks in advance

Reply to this Message

Ken Johnson - 24 Jan 2006 11:50 GMT

Hi Farooq,
this is only a stab in the dark, and I'm assuming you have less than
100 sheets.
K = K + 1
ActiveChart.SeriesCollection(1).XValues = "='MSC" & IIF(K>9,"0","") &
"'!R2C4:R14C4"
inside your loop.
Ken Johnson

Reply to this Message

Ken Johnson - 24 Jan 2006 11:52 GMT

Reply to this Message

Farooq Sheri - 24 Jan 2006 12:11 GMT

Thanks for the reply. First of my apologies for misleading you by naming my
sheets as MSC01, MSC02, MSC03,.. The names are all necessary starting with
MSC, it could be STP01, MOIP1 etc.

If I refer to sheets using index number then for each sheet I can find its
name using ActiveSheet.Name and then use this string in the formula. The
question is how to use a string within the formula. Also how can I find the
total number of sheets in workbook?

Thanks again

> Hi Farooq,
> this is only a stab in the dark, and I'm assuming you have less than
[quoted text clipped - 4 lines]
> inside your loop.
> Ken Johnson

Reply to this Message

Bob Phillips - 24 Jan 2006 12:00 GMT

For Each sh In Activeworkbbok.Worksheets
ActiveChart.SeriesCollection(1).XValues = "='" & sh.Name &
"'!R2C4:R14C4"
Next sh

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> I have about 100 sheets each named in terms of a node in a telecom network.
> For each node I have to graph a series of values and also find a max within a
[quoted text clipped - 11 lines]
>
> Thanks in advance

Reply to this Message

Farooq Sheri - 26 Jan 2006 08:00 GMT

Thanks Bob and Ken for your help.

Farooq

> For Each sh In Activeworkbbok.Worksheets
> ActiveChart.SeriesCollection(1).XValues = "='" & sh.Name &
[quoted text clipped - 29 lines]
> >
> > Thanks in advance

Reply to this Message