The "generic" name to which you refer is called the CodeName and is
accessible only via VBA code. There is no way in the Excel user interface to
reference the CodeName.
For example,
Debug.Print Worksheets("My Sheet").CodeName
To access the code name, you don't go through the Worksheet collection --
instead you access it directly. For example, suppose Sheet1 is named "My
Sheet". The following lines are functionally equivalent
Worksheets("My Sheet1").Range("A1").Value = 1234
Sheet1.Range("A1").Value =1234
The difference is that if the user changes the name of "My Sheet" to
something else, the first line of code will fail while the second line of
code will continue to work fine.
Broadly speaking, you cannot change the code name of a sheet. That said, it
can be done but is usually not a good idea to do so.
--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
"Tendresse" wrote in message
...
Hi all, I hope someone would be able to help me with my question.
I have a workbook containing 5 worksheets: Main Sheet, July, June, May,
April. In each of these worksheets I defined a couple of ranges using
InsertNameDefine. These ranges are Range1 and Range2.
In the Main Sheet I have a formula that refers to values located in the
next
adjacent 3 worksheets (July, June, May) as follows:
= SUMPRODUCT(--(July!Range1 = whatever),--(July!Range2 = whatever)) +
SUMPRODUCT(--(June!Range1 = wahetever),--(June!Range2= whatever)) +
SUMPRODUCT(--(May!Range1 = whatever),--(May!Range2= whatever))
In the above formula, I want to refer to the 3 worksheets July, June and
May
by their generic name (i.e. Sheet2, Sheet3 and Sheet4) not by the name I
assigned to them.
The reason is that I have a macro code that enables users to add one new
worksheet at the beginning of each month. So on August 1st, a new
worksheet
(called August) will be created after Main Sheet. Therefore, August will
become Sheet2 and July will then become Sheet3 as it will be shifted
one
step to the right. And the formula in the Main Sheet should then refer to
the
next 3 adjacent worksheets regardless of their names, in this case August,
July, June.
Im using Excel 2003.
Thanks in advance
Tendresse