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.

--
Rick (MVP - Excel)


"Robert Crandal" wrote in message
...
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
.
.
Case "Sheet50" :
' Do stuff

End Select

As you can see, there are at least 50 different cases which
makes for a lot of code. Can anyone think of a more compact,
better, or faster way of testing for a bunch of sheet codenames??

thank u

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



"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)

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.
--
Cheers,
Ryan


"Robert Crandal" wrote:

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
.
.
Case "Sheet50" :
' Do stuff

End Select

As you can see, there are at least 50 different cases which
makes for a lot of code. Can anyone think of a more compact,
better, or faster way of testing for a bunch of sheet codenames??

thank u



.

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


--
Regards,

OssieMac

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" wrote in message
...
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



"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)

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

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" wrote in message
...
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



"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)

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:="", _
compa=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




Robert Crandal wrote:

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

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


--

Dave Peterson

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


--
Regards,

OssieMac


"Robert Crandal" wrote:

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

Thank you everyone for so many wonderful solutions! Ya'll
are simply the BEST!!!!!

Rober C.