Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've
checked the spelling, even pasted it straight from the name box into the
code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any
variant on that I could think of.
Dave.

"Jacob Skaria" wrote:

Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


"Dave" wrote:

Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

"Jacob Skaria" wrote:

This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


"Dave" wrote:

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name < A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.