Thread
:
Named range
View Single Post
#
9
Posted to microsoft.public.excel.programming
Tushar Mehta
external usenet poster
Posts: 1,071
Named range
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 ,
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
.
.
.
Reply With Quote
Tushar Mehta
View Public Profile
Find all posts by Tushar Mehta