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)