Fastest way to select a sheet codename

  • Thread starter Thread starter Robert Crandal
  • Start date Start date
R

Robert Crandal

I have the following code (which really bothers me):


Select Case ws.CodeName:

Case "Sheet1" :
' Do stuff
Case "Sheet2" :
Case "Sheet3" :
' Do stuff
Case "Sheet4" :
' Do stuff
Case "Sheet5" :
' Do stuff
 
Whether you can shorten that up or not is highly dependent on what each of
the "Do stuff" are. If the code for them is all the same except for the
CodeName reference, then you can shorten this up dramatically... if they are
differences other than in the CodeName, then it depends on what those
differences are as to whether you can simplify the code or not... if there
is some serial regularity about numbers that appear in them, then maybe a
mathematical expression can be used to simplify the loops... but if the "Do
stuff" is wildly different from each other, then you have to specify each
one separately. We would have to see the "Do stuff" to decide.
 
Sure, I'll try to elaborate on my "Do stuff" code.

Basically, if the "ws.CodeName" is between Sheet1 and Sheet25,
then the "Do stuff" is as follows:

Set pWS = Sheet51

Then, if "ws.CodeName" is between Sheet26 and Sheet50, then
the "Do stuff" is:

Set pWS = Sheet52

It would be nice if I could use the "Select Case" statement as
follows (but I don't think it will work):

Select Case ws.CodeName

Case "Sheet1" to "Sheet25":

Set pWS = Sheet51

Case "Sheet26" to "Sheet50":

Set pWS = Sheet52

End Select
 
Does each worksheet have the exact same code or are they all totallly
different? How is this procedure called? Can the individual cases be call a
independent modules?

If each case code is different you can try something like this:

Select Case ws.CodeName
Case "Sheet1": Call Sheet1Sub
Case "Sheet2": Call Sheet2Sub
...
End Select

I know it doesn't get rid of the select case statement.

Hope this helps! If so, click "YES" below.
 
Hi robert,

My questions are similar to Ryan's. However, if all worksheets have
different code then place the code in separate subs and call the subs based
on the numeric value suffix of each sheet CodeName. The following code is an
example of how this can be done. (You probably do not want the loop. Just
there for the demo.)

Sub CallSubs()

Dim ws As Worksheet
For Each ws In Worksheets

Application.Run "CodeForSht" & Mid(ws.CodeName, 6)

Next ws
End Sub

Sub CodeForSht1()
MsgBox "CodeForSht1"
End Sub

Sub CodeForSht2()
MsgBox "CodeForSht2"
End Sub
Sub CodeForSht3()
MsgBox "CodeForSht3"
End Sub
Sub CodeForSht4()
MsgBox "CodeForSht4"
End Sub
Sub CodeForSht5()
MsgBox "CodeForSht5"
End Sub
Sub CodeForSht10()
MsgBox "CodeForSht10"
End Sub
Sub CodeForSht11()
MsgBox "CodeForSht11"
End Sub
Sub CodeForSht12()
MsgBox "CodeForSht12"
End Sub

If Groups of worksheets have the same code then you could also use the
numeric to group them in the Case statement.

Select Case Val(Mid(ws.CodeName, 6))
Case 1, 3, 5, 7
'Do stuff
Case 2, 4, 5, 6
'Do Stuff
End Select
 
Maybe something like

n = Val(Replace("ws.CodeName", "Sheet", ""))
or
n = val(Mid$("ws.CodeName",6,5)

If n = 0 then
?
elseif n <= 25 then
Set pWS = Sheet51
elseif n <= 50 then
Set pWS = Sheet52
else
?
end if

Regards,
Peter T


Robert Crandal said:
Sure, I'll try to elaborate on my "Do stuff" code.

Basically, if the "ws.CodeName" is between Sheet1 and Sheet25,
then the "Do stuff" is as follows:

Set pWS = Sheet51

Then, if "ws.CodeName" is between Sheet26 and Sheet50, then
the "Do stuff" is:

Set pWS = Sheet52

It would be nice if I could use the "Select Case" statement as
follows (but I don't think it will work):

Select Case ws.CodeName

Case "Sheet1" to "Sheet25":

Set pWS = Sheet51

Case "Sheet26" to "Sheet50":

Set pWS = Sheet52

End Select
Click to expand...
 
It would be nice if I could use the "Select Case" statement as
follows (but I don't think it will work):

Select Case ws.CodeName

Case "Sheet1" to "Sheet25":

Set pWS = Sheet51

Case "Sheet26" to "Sheet50":

Set pWS = Sheet52

End Select
Click to expand...

You could do the above **IF** the string were the same length... this would
mean your first nine CodeNames would have had to have been Sheet01, Sheet02,
etc. You can cheat a little to achieve the above like this (note the 01 in
the first text string)...

Select Case "Sheet" & Format(Mid(ws.CodeName, 6), "00")
Case "Sheet01" To "Sheet25":
Set pWS = Sheet51
Case "Sheet26" To "Sheet50":
Set pWS = Sheet52
End Select

However, I am pretty sure this one-liner will do the identical thing that
the above code does...

Set pWS = Worksheets("Sheet" & (51 - (Mid(ws.CodeName, 6) > 25)))

--
Rick (MVP - Excel)


Robert Crandal said:
Sure, I'll try to elaborate on my "Do stuff" code.

Basically, if the "ws.CodeName" is between Sheet1 and Sheet25,
then the "Do stuff" is as follows:

Set pWS = Sheet51

Then, if "ws.CodeName" is between Sheet26 and Sheet50, then
the "Do stuff" is:

Set pWS = Sheet52

It would be nice if I could use the "Select Case" statement as
follows (but I don't think it will work):

Select Case ws.CodeName

Case "Sheet1" to "Sheet25":

Set pWS = Sheet51

Case "Sheet26" to "Sheet50":

Set pWS = Sheet52

End Select
Click to expand...
 
First, I wouldn't use the default codenames--Sheet1, Sheet2, ...

I'd rename the codenames to something significant and something consistent in
format.

If the sheets represented customers, I'd use their ID and maybe some counter.

Then I could check the first xx characters (or last yy characters) to decide
what to do.

Catego001, Catego002, ...
Prices001, Prices002, ...
Custom001, Custom002, ...

select case left(lcase(ws.codename),6)
case is = "catego"
....

I think it would make it easier than trying to remember how the sheets are
divided.

You can show the properties of the worksheet (in the VBE) and change the (name)
property to what you want.

=========
But (I wouldn't use this), you could still strip out the digits from all the
codenames with something like:

Option Explicit
Sub testme()

Dim ws As Worksheet
Dim pWs As Worksheet
Dim LastNumber As Long

Set ws = ActiveSheet

LastNumber = -99
On Error Resume Next
LastNumber = CLng(Replace(expression:=ws.CodeName, _
Find:="sheet", Replace:="", _
compare:=vbTextCompare))
On Error GoTo 0

Set pWs = Nothing
If LastNumber = -99 Then
'ignore it
Else
Select Case LastNumber
Case 1 To 25
Set pWs = Sheet51
Case 26 To 50
Set pWs = Sheet52
End Select
End If

If pWs Is Nothing Then
MsgBox "nothing!"
Else
MsgBox pWs.Name
End If

End Sub
 
If more than one sheet has the same actions taken with the data, then you
can group those sheets under one case:

Select Case ws.CodeName:

Case "Sheet1", "Sheet11", "Sheet12" :
' Do stuff
Case "Sheet2", "Sheet22", "Sheet32" :
Case "Sheet3". "Sheet23", "Sheet33" :
' Do stuff
Case "Sheet4" :
' Do stuff
Case "Sheet5" :
' Do stuff
 
You could give this a try if all your sheet codenames start with "Sheet"!

Sub ProcessSheets()
Dim i As Integer
Dim pWS As Worksheet
For i = 1 To ActiveWorkbook.Sheets.Count
Select Case CInt(Mid$(Sheets(i).CodeName, 6))
Case 1 To 25: Set pWS = Sheet51
Case Is > 25: Set pWS = Sheet52
End Select
Next
End Sub

HTH
Kind regards,
Garry
 
Hi again Robert,

Answering your last post you could do it using the following method.

Select Case Val(Mid(ws.CodeName, 6))
Case 1 To 25
Set pWS = Sheet51
Case 26 To 50
Set pWS = Sheet52
End Select
 
Thank you everyone for so many wonderful solutions! Ya'll
are simply the BEST!!!!!

Rober C.
 
Back
Top