assining a varaible to the value in another sheet.
 

Hi,

I have 2 sheets in a work book. In Sheet1 I have something like
Number of sets = 27. This number (27) falls into the D7 of sheet1. I
have defined a name NSETS to this cell with this kind of expression
=Sheet1!$D$7. But now I need to use this number in the vba code I have
for the Sheet2. In Sheet2 I have the code like this

Set WBook = Application.Caller.Parent.Parent
nmodules = WBook.Sheets("Sheet1").Range("NSETS").Value

But this is not working. What I am I doing wrong?

Thanks
shi

assining a varaible to the value in another sheet.
 

Is this code in a UDF? If not, it is unlikely that
Application.Caller.Parent.Parent will work.

Try this

nmodules = ThisWorkbook.Sheets("Sheet1").Range("NSETS").Value

if everything is in the same workbook

or even

nmodules = ActiveWorkbook.Sheets("Sheet1").Range("NSETS").Val ue

if you will use it on other workbooks, activating it before



--

HTH

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


"shishi" wrote in message
oups.com...
Hi,

I have 2 sheets in a work book. In Sheet1 I have something like
Number of sets = 27. This number (27) falls into the D7 of sheet1. I
have defined a name NSETS to this cell with this kind of expression
=Sheet1!$D$7. But now I need to use this number in the vba code I have
for the Sheet2. In Sheet2 I have the code like this

Set WBook = Application.Caller.Parent.Parent
nmodules = WBook.Sheets("Sheet1").Range("NSETS").Value

But this is not working. What I am I doing wrong?

Thanks
shi