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