make string variable reference VBA sheet name
 

Here's the scenario:

In my workbook, I have a series of 12 chartsheets which have the names "Jan
Duration" through "Dec Duration". These sheets graph monthly information. In
the VBE, these sheets are named "cshtMonth01Dur" through "cshtMonth12Dur",
so that I can loop from 1 to 12 and update the chart title on each of the
sheets. Here's a portion of my code:

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = "cshtMonth" & strMonthNumber & "Dur"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter

When it gets to the 'Select' line, it triggers "run-time error '9':
Subscript out of range"

I know that the 'Sheets(strChartSheetName).Select' should read something
like 'Sheets("Jan Duration").Select', but I can't see how to retrieve the
name of a sheet when it's referenced by a variable. Any and all help would
be greatly appreciated.

Tom

make string variable reference VBA sheet name
 

Tom,

Why not use the sheet name?

Dim aryMonths()

aryMonths = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

For bytSheetLoopCounter = 1 To 12

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = aryMonths(bytSheetLoopCounter) & " Duration"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Deiley" wrote in message
...
Here's the scenario:

In my workbook, I have a series of 12 chartsheets which have the names
"Jan
Duration" through "Dec Duration". These sheets graph monthly information.
In
the VBE, these sheets are named "cshtMonth01Dur" through "cshtMonth12Dur",
so that I can loop from 1 to 12 and update the chart title on each of the
sheets. Here's a portion of my code:

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = "cshtMonth" & strMonthNumber & "Dur"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter

When it gets to the 'Select' line, it triggers "run-time error '9':
Subscript out of range"

I know that the 'Sheets(strChartSheetName).Select' should read something
like 'Sheets("Jan Duration").Select', but I can't see how to retrieve the
name of a sheet when it's referenced by a variable. Any and all help would
be greatly appreciated.

Tom

make string variable reference VBA sheet name
 

Hi Tom;

I can think of two ways that might help. (1) If the sheets
are always the same and in the same order you could move
tot he Index method to get the sheets such as Sheets
(1).select or (2) You could reuse the exact variablization
that created their name in the first place. In the
example below I'm using day(date) as the variable.

MySheetVariableName = "Sheet One" & Day(Date)

Sheets("Sheet One" & Day(Date)).Select

On other thing that might help is to go to the activate
method.

Sheets("MySheet").activate

Thanks,

Greg


-----Original Message-----
Here's the scenario:

In my workbook, I have a series of 12 chartsheets which
have the names "Jan
Duration" through "Dec Duration". These sheets graph
monthly information. In
the VBE, these sheets are named "cshtMonth01Dur"
through "cshtMonth12Dur",
so that I can loop from 1 to 12 and update the chart
title on each of the
sheets. Here's a portion of my code:

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = "cshtMonth" & strMonthNumber
& "Dur"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter

When it gets to the 'Select' line, it triggers "run-time
error '9':
Subscript out of range"

I know that the 'Sheets(strChartSheetName).Select' should
read something
like 'Sheets("Jan Duration").Select', but I can't see how
to retrieve the
name of a sheet when it's referenced by a variable. Any
and all help would
be greatly appreciated.

Tom


.

make string variable reference VBA sheet name
 

I originally was going to reference the sheet name, but I'm not the only
person who will be using this workbook. I realized that another recipient of
this workbook could change the sheet name, causing the code would fail.

"Bob Phillips" wrote in message
...
Tom,

Why not use the sheet name?

Dim aryMonths()

aryMonths = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

For bytSheetLoopCounter = 1 To 12

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = aryMonths(bytSheetLoopCounter) & " Duration"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Deiley" wrote in message
...
Here's the scenario:

In my workbook, I have a series of 12 chartsheets which have the names
"Jan
Duration" through "Dec Duration". These sheets graph monthly
information.
In
the VBE, these sheets are named "cshtMonth01Dur" through
"cshtMonth12Dur",
so that I can loop from 1 to 12 and update the chart title on each of
the
sheets. Here's a portion of my code:

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = "cshtMonth" & strMonthNumber & "Dur"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter

When it gets to the 'Select' line, it triggers "run-time error '9':
Subscript out of range"

I know that the 'Sheets(strChartSheetName).Select' should read something
like 'Sheets("Jan Duration").Select', but I can't see how to retrieve
the
name of a sheet when it's referenced by a variable. Any and all help
would
be greatly appreciated.

Tom

make string variable reference VBA sheet name
 

Good suggestions, but the 'Sheets' syntax needs to see either:

1. the 'Jan Duration', not the 'cshtMonth01Dur', or
2. the index number of the sheet

What I'm trying to do is use the 'csht...' info to retrieve the sheet name
to use in the 'Sheets' syntax. I suppose I could use the Index option and
run the risk that the user won't add or delete any sheets in the workbook.
I'll have to think about that...
Tom


"Gjones" wrote in message
...
Hi Tom;

I can think of two ways that might help. (1) If the sheets
are always the same and in the same order you could move
tot he Index method to get the sheets such as Sheets
(1).select or (2) You could reuse the exact variablization
that created their name in the first place. In the
example below I'm using day(date) as the variable.

MySheetVariableName = "Sheet One" & Day(Date)

Sheets("Sheet One" & Day(Date)).Select

On other thing that might help is to go to the activate
method.

Sheets("MySheet").activate

Thanks,

Greg


-----Original Message-----
Here's the scenario:

In my workbook, I have a series of 12 chartsheets which
have the names "Jan
Duration" through "Dec Duration". These sheets graph
monthly information. In
the VBE, these sheets are named "cshtMonth01Dur"
through "cshtMonth12Dur",
so that I can loop from 1 to 12 and update the chart
title on each of the
sheets. Here's a portion of my code:

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = "cshtMonth" & strMonthNumber
& "Dur"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter

When it gets to the 'Select' line, it triggers "run-time
error '9':
Subscript out of range"

I know that the 'Sheets(strChartSheetName).Select' should
read something
like 'Sheets("Jan Duration").Select', but I can't see how
to retrieve the
name of a sheet when it's referenced by a variable. Any
and all help would
be greatly appreciated.

Tom


.

make string variable reference VBA sheet name
 

Tom,

Here's an alternative deriving the sheetname from the codename

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = "cshtMonth" & strMonthNumber & "Dur"
Worksheets(CStr(ActiveWorkbook.VBProject. _
VBComponents(strChartSheetName). _
Properties("Name"))).Select

Next bytSheetLoopCounter


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Deiley" wrote in message
...
I originally was going to reference the sheet name, but I'm not the only
person who will be using this workbook. I realized that another recipient
of
this workbook could change the sheet name, causing the code would fail.

"Bob Phillips" wrote in message
...
Tom,

Why not use the sheet name?

Dim aryMonths()

aryMonths = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

For bytSheetLoopCounter = 1 To 12

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = aryMonths(bytSheetLoopCounter) & " Duration"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Deiley" wrote in message
...
Here's the scenario:

In my workbook, I have a series of 12 chartsheets which have the names
"Jan
Duration" through "Dec Duration". These sheets graph monthly
information.
In
the VBE, these sheets are named "cshtMonth01Dur" through
"cshtMonth12Dur",
so that I can loop from 1 to 12 and update the chart title on each of
the
sheets. Here's a portion of my code:

For bytSheetLoopCounter = 1 To 12
If bytSheetLoopCounter < 10 Then
strMonthNumber = "0" & bytSheetLoopCounter
Else
strMonthNumber = bytSheetLoopCounter
End If

strChartSheetName = "cshtMonth" & strMonthNumber & "Dur"
Sheets(strChartSheetName).Select

Next bytSheetLoopCounter

When it gets to the 'Select' line, it triggers "run-time error '9':
Subscript out of range"

I know that the 'Sheets(strChartSheetName).Select' should read
something
like 'Sheets("Jan Duration").Select', but I can't see how to retrieve
the
name of a sheet when it's referenced by a variable. Any and all help
would
be greatly appreciated.

Tom