make string variable reference VBA sheet name

T

Tom Deiley

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
 
B

Bob Phillips

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

Gjones

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
 
T

Tom Deiley

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

Tom Deiley

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
 
B

Bob Phillips

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