Not sure what you're trying to do .. I'm not aware that sheets  have a
"codename" ... but....

.. You seem to be trying to second guess a user  that can alter a workbook.
Trust me... It's not possible.

Given that...

Assuming that the number of worksheets  isn't going to alter, you can
reference a sheet number in the sheets collection.

Instead of using a literal... "volvo", "dodge", "honda" ... You can use a
number. I'm unsure if this number changes if you just add sheets but I know
it changes if you delete sheets above that number. Say you had a sheet
"volvo" and you referenced it as sheet number 4. If you deleted a sheet
below 4 then this would change it's number. I'm not sure what to. I
personally don't see a *totally* foolproof way around this. Maybe more
knowledgable people will jump in.

I think I'd go for a cell in an unused space with a white font colour so
that the text in it couldn't be seen and loop through the sheets looking at
that cell and testing for its value with a select  case statement. This would
effectively create a "code" for that sheet to test for ... *But* .. the user
could still dump over that cell and then all bets are off.

Actually, the reason to use the "Sheets Code Name" is to get around
the user  altering the workbook. See:

www.ozgrid.com/VBA/excel-vba-sheet-names.htm

Why not just use the name property of the sheet's codename? For
example, the following sets of code give the same result:

n1 = 0
For Each oSheet In Sheets(Array("A", "C"))
n1 = n1 + oSheet.[A1]
Next oSheet
Debug.Print "n1 = " & n1 & vbCrLf

n2 = 0
For Each oSheet In Sheets(Array(Sheet1.Name, Sheet3.Name))
n2 = n2 + oSheet.[A1]
Next oSheet
Debug.Print "n2 = " & n2 & vbCrLf

The web site you referenced nicely explains the difference between
using a worksheets  codename over the label  that a user  assignes to the
tab.

Rather than referencing Sheets("Honda").Select, I would reference
Sheet3.Select This is good...

The problem I'm having is selecting worksheets that are not in any
order (sheet3, sheet7 and sheet8). The command:

Sheets(Array("volvo", "dodge", "honda")).Select

works really well... is there a way I can substitute sheet3 for volvo?

In the meantime, I'll try to work  through the solutions you have
provided - I'm a beginner in VBA.

As I said, try using the name property of the codename, as in:

Sheets(Array(Sheet3.Name, Sheet7.Name, Sheet8.Name)).Select

...where Sheet3.Name is "volvo". But if they change the name of the
worksheet, the above formula would pick up the new name.

FWIW I think that if the user  can change the name of the sheet then the
possibility of them inserting a sheet just where you don't want them to
should be catered for.

I'd still go for a solution that uses some feature / cell that is unique to
each sheet and isn't likely to be altered.

"the possibility of them inserting a sheet just where you don't
want them to should be catered for"

solution it is; despite that at first codenames are
similar to the sheet's tab  names, a sheet keeps its codename unless
programmatically changed (with the likes of:
Workbooks("test2.xls").VBProject.VBComponents("Sheet1").Name =
"AnotherName"
) or changed manually in the VBE using the sheet's properties window.
It'll keep its codename through other sheet insertions and other sheet
deletions. Not to be confused also with a sheet's index number
(Sheets(1), Sheets(3) etc. which happily changes!)

Good information ... I've always wondered about that!

Thanks.

I tried this:

Opened a new wkbk
dragged sheet 3 tab  before sheet 2
Went into the VBA Editor

The codenames and names  are now

Sheet1 (Sheet1)
Sheet2 (Sheet3)
Sheet3 (Sheet2)

It looks to me as though the codenames have changed.

: Opened a new wkbk
: dragged sheet 3 tab  before sheet 2
: Went into the VBA Editor
:
: The codenames and names  are now
:
: Sheet1 (Sheet1)
: Sheet2 (Sheet3)
: Sheet3 (Sheet2)
:
: It looks to me as though the codenames have changed.

When I do the same thing, I get this in the Project
Explorer window. I am running Excel 2002 SP3.

Sheet1 (Sheet1)
Sheet2 (Sheet2)
Sheet3 (Sheet3)
ThisWorkBook

Are your sheets  in the order:

Sheet1
Sheet3
Sheet2

This was the effect of the drag operation I did. If they are not you have not
changed anything, have you?

: Are your sheets  in the order:
:
: Sheet1
: Sheet3
: Sheet2

They were yesterday. Today, I opened a saved version of the
book and got your results. I'm certain I didn't misread it
yesterday, but now I have this.

Sheet1 (Sheet1)
Sheet2 (Sheet3)
Sheet3 (Sheet2)
ThisWorkBook

: They were yesterday. Today, I opened a saved version of the
: book and got your results. I'm certain I didn't misread it
: yesterday, but now I have this.
:
: Sheet1 (Sheet1)
: Sheet2 (Sheet3)
: Sheet3 (Sheet2)
: ThisWorkBook

I just tried it again. I moved sheet1 to the last position.
In order to get the Project Explorer window to change, I had
to close the workbook and open it again. Now I have this listed.

Sheet1 (Sheet3)
Sheet2 (Sheet2)
Sheet3 (Sheet1)
ThisWorkbook

So, either the Project Explorer does not show the secret
code name of sheets  or it does and the advice given in
www.ozgrid.com/VBA/excel-vba-sheet-names.htm is wrong.

Yes indeed, I got the same after saving and reopening the workbook!
So it seems I was wrong.. but I tried this: I added a standard code
module, put a tiny sub into into it then tried rearranging the sheets
as before - now the codenames stuck to their respective sheets. I
don't know whether this would still work  with a bit of code in one of
the sheet modules.

Thank you for the solution. Very much appreciated.

A solution might be to give your sheets  a unique name in the VBA editor, ie
wsVolvo etc.
Then the name displayed for the user  or the altered name don't really matter
any more and the command for selecting a sheet would simply become:
wsVolvo.Select