Using CodeName when Selecting Multi-Tabs

[Dean]

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

 

[Tom Ogilvy]

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

--
regards,
Tom Ogilvy

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

 

[Dean]

Thanks Tom, very much. Dean

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

--
regards,
Tom Ogilvy



Dean

 

[merry_fay]

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

 

[Chip Pearson]

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

 

[Peter T]

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