Sheet-level range name discrepancy (2000 SR1)

[JIMbythebay]

Hi Folks,

I loop through six sheets and pass each sheet to a procedure as an argument (WhichSheet in the code sample). That procedure applies some range names. Therefore, each sheet gets treated the same way. Below is a portion of that procedure:

<pre> With WhichSheet
.Range("Norm1", "Norm11").Name = "Y"
.Range("CoA", "CoD").Name = "Coeffs"
.Range("Mod1", "Mod11").Name = "Model"

End With ' WhichSheet


</pre>


After this code is executed, the ranges named "Coeffs" and "Model" are properly assigned on a per-sheet basis, but the range named Y is assigned only to the last sheet.

That is, if you pick any particular sheet (say Feature2) and choose Insert, Name, Define, the entry for "Coeffs" and "Model" each have "Feature2" listed next to them, and the sheet specification in the "Refers to:" field depends on which sheet you were on when you chose Insert, Name, Define. However, the listing for Y does not have a sheet listed next to it in the list box, and the sheet specification in the "Refers to:" field is "Feature6!..." regardless of what sheet you were on when you chose Insert, Name, Define. (see picture)

I cannot figure out any reason behind this behavior, any ideas?!? Thanks!

[JIMbythebay]

Never mind, I answered my own question. I have to modify the code as follows:

<pre>.Range("Norm1", "Norm11").Name = WhichSheet.Name & "!Y"
etc.
</pre>


The other two range names are left-overs from when I was developing and named the ranges manually, on a per-sheet basis. After deleting the "Coeffs" and "Model" range names from Insert Name, Define, these ranges also exhibited the strange naming behavior.