Take a look at http://www.xldynamic.com/source/xld.Names.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Geoff" wrote in message
...
Hi
So that's why it failed on the first sheet! I've got
some further reading and testing to do!

Thank you very much for the pointers, your time is
appreciated.

Geoff
-----Original Message-----
In article ,
says...

sheet level names? yes as in Insert- Name - Define
=Sheet1!$B$6:$B$8
='Sheet1 (2)'!$B$6:$B$8 etc

No, unfortunately, that doesn't create a sheet level
name. To create a
sheet level name you have to use Sheet1!myName as the
name. What you
have is a workbook level name. When you copy the
worksheet, XL creats
a sheet level name within the copy.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi
Still no I'm afraid as per:
Sub test()
Dim x As Name, i As Integer
For i = 1 To Sheets.Count
On Error Resume Next
Set x = Nothing
Set x = Sheets(i).Names("myname")
On Error GoTo 0
MsgBox "Name myName exists: " & Not (x Is Nothing)
Next
End Sub

The returns a
sheet1 = false
sheet2 = true
sheet3 = true
sheet4 = false
sheet5 = false

sheet1 should read true

sheet level names? yes as in Insert- Name - Define
=Sheet1!$B$6:$B$8
='Sheet1 (2)'!$B$6:$B$8 etc


Geoff

-----Original Message-----
How does it fail to provide the correct information?

While you haven't shared that information, I can
guess.
Add a
Set x=nothing
before the Set x = Sheets(i)... statement.

Of course, all of this discussion assumes you are
using
sheet level
names.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Sorry on further testing this did not provide the
correct
answers.

Set up is this:
Starting with 3 sheets in the workbook:

Create named range "myname" on sheet1 B6 to B8
Copy before sheet2 twice
This gives first 3 sheets with named range, last 2
without.
My adaptation, nor the original, works correctly:
Sub test()
Dim x As Name, i As Integer
For i = 1 To Sheets.Count
On Error Resume Next
Set x = Sheets(i).Names("myname")
On Error GoTo 0
MsgBox "Name myName exists: " & Not (x Is
Nothing)
Next
End Sub

Can you assist further please?

Geoff

-----Original Message-----
Hi,
Great - I had missed out on the On Error Resume
part
in
my own testing.

Many thanks

Geoff
-----Original Message-----
Sub testIt()
Dim x As Name
On Error Resume Next
Set x = ActiveSheet.Names("myName")
On Error GoTo 0
MsgBox "Name myName exists: " & Not (x Is
Nothing)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <002c01c54345$59d2f890
,
says...
There are 2 named ranges, Quantity and Amount
on
Sheet1.

Sheet1 is then copied 3 times (the same names
are
also
therefore copied).

If sheet5 is inserted (not copied) how can I
programatically detect if sheet5 has the named
range
Quantity without the use of absolute cell
references?

I'd be very grateful of advice.


Geoff

.

.


.


.