Naming a worksheet as a variable in a Active Chart Series

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

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

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

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)

"Farooq Sheri" <[email protected]> wrote in message
news:[email protected]...
> 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

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

"Ken Johnson" wrote:

> 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
>
>

Thanks Bob and Ken for your help.

Farooq

"Bob Phillips" wrote:

>
> 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)
>
> "Farooq Sheri" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>