Okay, to get this working I've had to change the davv... reference t
the actual worksheet name instead, like: 'Validation Values'!C1. Stil
can't understand why I can't just reference a variable but I need t
get the working. I'm sure it's something quite basic and fundamental i
relation to the way you should use/declare variables so I would stil
appreciate any advice to prevent similar issues in the future?

Thanks,

Adria

--
Kobayash
-----------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...info&userid=87
View this thread: http://www.excelforum.com/showthread.php?threadid=27358

if davvsheet is a variable holding the text Sheet2

then

Sub BBTest()
Dim DAbook as Workbook
Dim davvsheet as String
davvsheet = "Sheet2"
Set DAbook = Workbooks("Book1.xls")
DAbook.Names.Add Name:="Lookuplist2", RefersToR1C1:= _
"=" & davvsheet & "!c1"
End Sub

would work, but you were already given that answer???

a possibibility is that the sheet name has a space in it. Then you would
need

"='" & davvsheet & "'!c1"

so the name will be enclosed in single quotes.
--
Regards,
Tom Ogilvy


"Kobayashi" wrote in message
...

Okay, to get this working I've had to change the davv... reference to
the actual worksheet name instead, like: 'Validation Values'!C1. Still
can't understand why I can't just reference a variable but I need to
get the working. I'm sure it's something quite basic and fundamental in
relation to the way you should use/declare variables so I would still
appreciate any advice to prevent similar issues in the future?

Thanks,

Adrian


--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile:
http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=273587