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
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!