I am hoping that someone is experienced in using the Sheet CodeNames when
selecting Multiple Sheets. I know that you can use Sheet5.Select to go to
sheet 5, even though the sheet may have been renamed by the user, sheet 5
remains constant in Properties. I can select multiple sheets, using
Sheets(Array("MySheet", "YourSheet", "ThereSheet")).Select. But, I want to
be able to use the CodeName instead, I'm unable to find any references to
doing so. By using the CodeName, I wouldn't have to track the changes in the
user applied sheet names. Can anyone help me on this? I appreciate it. Dean

sheets(array(Sheet1.Name, Sheet5.Name, Sheet7.Name)).Select

--
regards,
Tom Ogilvy


"Dean" wrote in message
...
I am hoping that someone is experienced in using the Sheet CodeNames when
selecting Multiple Sheets. I know that you can use Sheet5.Select to go to
sheet 5, even though the sheet may have been renamed by the user, sheet 5
remains constant in Properties. I can select multiple sheets, using
Sheets(Array("MySheet", "YourSheet", "ThereSheet")).Select. But, I want
to
be able to use the CodeName instead, I'm unable to find any references to
doing so. By using the CodeName, I wouldn't have to track the changes in
the
user applied sheet names. Can anyone help me on this? I appreciate it.
Dean

Thanks Tom, very much. Dean

"Tom Ogilvy" wrote:

sheets(array(Sheet1.Name, Sheet5.Name, Sheet7.Name)).Select

--
regards,
Tom Ogilvy


"Dean" wrote in message
...
I am hoping that someone is experienced in using the Sheet CodeNames when
selecting Multiple Sheets. I know that you can use Sheet5.Select to go to
sheet 5, even though the sheet may have been renamed by the user, sheet 5
remains constant in Properties. I can select multiple sheets, using
Sheets(Array("MySheet", "YourSheet", "ThereSheet")).Select. But, I want
to
be able to use the CodeName instead, I'm unable to find any references to
doing so. By using the CodeName, I wouldn't have to track the changes in
the
user applied sheet names. Can anyone help me on this? I appreciate it.
Dean

Hi,

This si just what I'm looking for, but is there a shorter way of writing it?

I need to select sheets 1 to 17 which becomes a lengthy bit of code!

Thanks
merry_fay

"Dean" wrote:

Thanks Tom, very much. Dean

"Tom Ogilvy" wrote:

sheets(array(Sheet1.Name, Sheet5.Name, Sheet7.Name)).Select

--
regards,
Tom Ogilvy


"Dean" wrote in message
...
I am hoping that someone is experienced in using the Sheet CodeNames when
selecting Multiple Sheets. I know that you can use Sheet5.Select to go to
sheet 5, even though the sheet may have been renamed by the user, sheet 5
remains constant in Properties. I can select multiple sheets, using
Sheets(Array("MySheet", "YourSheet", "ThereSheet")).Select. But, I want
to
be able to use the CodeName instead, I'm unable to find any references to
doing so. By using the CodeName, I wouldn't have to track the changes in
the
user applied sheet names. Can anyone help me on this? I appreciate it.
Dean

Try something like

Dim Arr() As String
Dim N As Long
ReDim Arr(1 To 17)
For N = 1 To 17
Arr(N) = ThisWorkbook.VBProject. _
VBComponents("Sheet" & CStr(N)).Name
Next N
Worksheets(Arr).Select

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Wed, 17 Mar 2010 12:53:02 -0700, merry_fay
wrote:

Hi,

This si just what I'm looking for, but is there a shorter way of writing it?

I need to select sheets 1 to 17 which becomes a lengthy bit of code!

Thanks
merry_fay

"Dean" wrote:

Thanks Tom, very much. Dean

"Tom Ogilvy" wrote:

sheets(array(Sheet1.Name, Sheet5.Name, Sheet7.Name)).Select

--
regards,
Tom Ogilvy


"Dean" wrote in message
...
I am hoping that someone is experienced in using the Sheet CodeNames when
selecting Multiple Sheets. I know that you can use Sheet5.Select to go to
sheet 5, even though the sheet may have been renamed by the user, sheet 5
remains constant in Properties. I can select multiple sheets, using
Sheets(Array("MySheet", "YourSheet", "ThereSheet")).Select. But, I want
to
be able to use the CodeName instead, I'm unable to find any references to
doing so. By using the CodeName, I wouldn't have to track the changes in
the
user applied sheet names. Can anyone help me on this? I appreciate it.
Dean

Not sure why you need to involve codenames if you only want to select sheets
1 to 17. Maybe somehting like this will do

Dim i as long
Redim arr(1 To 17)
For i = 1 To Ubound(arr)
arr(i) = i
Next
Sheets(arr).select

Regards,
Peter T


"merry_fay" wrote in message
...
Hi,

This si just what I'm looking for, but is there a shorter way of writing
it?

I need to select sheets 1 to 17 which becomes a lengthy bit of code!

Thanks
merry_fay

"Dean" wrote:

Thanks Tom, very much. Dean

"Tom Ogilvy" wrote:

sheets(array(Sheet1.Name, Sheet5.Name, Sheet7.Name)).Select

--
regards,
Tom Ogilvy


"Dean" wrote in message
...
I am hoping that someone is experienced in using the Sheet CodeNames
when
selecting Multiple Sheets. I know that you can use Sheet5.Select to
go to
sheet 5, even though the sheet may have been renamed by the user,
sheet 5
remains constant in Properties. I can select multiple sheets, using
Sheets(Array("MySheet", "YourSheet", "ThereSheet")).Select. But, I
want
to
be able to use the CodeName instead, I'm unable to find any
references to
doing so. By using the CodeName, I wouldn't have to track the
changes in
the
user applied sheet names. Can anyone help me on this? I appreciate
it.
Dean