Debra
RngName is straight text without any reference to the sheet. The sheet
is "All Parts".

RngName = "AllParts"
Set Rng = .....................
Rng.Name = RngName

Note that RngName is defined as above BEFORE the With Sheets("All Parts")
construct.
Thanks for taking the time on a Sunday to help me with this. Otto
"Debra Dalgleish" wrote in message
...
How is RngName defined? If it includes the sheet name, the range name
will be sheet level, For example: 'All Parts'!AllParts

Otto Moehrbach wrote:
Debra
Bingo! With BadSheet selected, Insert - Name - Define showed
"BadSheet"
at the far right. What does that mean? When I then clicked on
AllParts,
the reference was to 'All Parts'#REF! (The list AllParts is on sheet
"All
Parts").
Can you please, please, please explain to me what I did to get into this
mess? And what I need to watch out for so that I don't do it again?
Does
it have to do with references while in a With - End With construct? I
ask
that because I was in a With Sheets("All Parts") construct when I named
the
list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...

Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:

Debra
Thanks for your response. I checked both lists and both references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a new

blank

sheet and rebuilt the Data Validation cells but in the new sheet

(without

copying anything from BadSheet). All works well. Does this indicate
to

you

that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...


Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:


Tom
Thanks for your reply. The naming of the list is done by code.

The

code copies/pastes (shorter) lists from several sheets to make one

longer


list. All this is within a For loop because there are two final
lists

to be


setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is

absolute.

Otto
"Tom Ogilvy" wrote in message
...



What does the formula for AllParts (refers to ) look like. Are you

using


relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
.. .



Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.



I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".



I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get

the

whole



list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,



and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different

list.

I go to the cell in BadSheet, clear the Data Validation, redo the

Data

Validation to =AllParts. I get the same 14 items from the first

AllParts



list.

Is this sheet corrupted or have I lost it? Thanks for your help.

Otto



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html