cell value from different worksheet
 

I have a user form that, based on the option button selected, a value needs
to be returned from a different cell in a separate worksheet. I get an out
of range error when I try the following code:

Sheets("Sample Selection").Select
ActiveSheet.Unprotect
Cells(2, 5) = Worksheets("SampleCalc").Cells(3, 45)

What am I doing wrong?

--
JP

cell value from different worksheet
 

subscript out of range errors means that the thing that caused the error doesn't
exist in the collection you're using.

Maybe it's a typo in the worksheet name (which line caused the error)?

or maybe the activeworkbook wasn't what you thought it was and so the worksheet
didn't exist in the real workbook that was active.

with activeworkbook.worksheets("sample selection")
.unprotect
.cells(2,5).value _
= activeworkbook.worksheets("samplecalc").cells(3,45 ).value
end with



JPCPA wrote:

I have a user form that, based on the option button selected, a value needs
to be returned from a different cell in a separate worksheet. I get an out
of range error when I try the following code:

Sheets("Sample Selection").Select
ActiveSheet.Unprotect
Cells(2, 5) = Worksheets("SampleCalc").Cells(3, 45)

What am I doing wrong?

--
JP

--

Dave Peterson

cell value from different worksheet
 

Your code should work but no need to select

With Sheets("Sample Selection")
.Unprotect
.Cells(2,5) = Sheets("SampleCalc").Cells(3,45)
End With


--

Regards,
Nigel




"JPCPA" wrote in message
...
I have a user form that, based on the option button selected, a value needs
to be returned from a different cell in a separate worksheet. I get an
out
of range error when I try the following code:

Sheets("Sample Selection").Select
ActiveSheet.Unprotect
Cells(2, 5) = Worksheets("SampleCalc").Cells(3, 45)

What am I doing wrong?

--
JP

cell value from different worksheet
 

Dave & Nigel,
Thanks for the responses -

Dave, I had the sheet spelled wrong (missing space). I also was referencing
the wrong cell - I did column,row, rather than row, column, hence the value
of the referenced cell was blank.

Nigel, Excellent thing to know. I've always selected the sheet first.
I'll note this for the future. Thank you.
--
JP


"Nigel" wrote:

Your code should work but no need to select

With Sheets("Sample Selection")
.Unprotect
.Cells(2,5) = Sheets("SampleCalc").Cells(3,45)
End With


--

Regards,
Nigel




"JPCPA" wrote in message
...
I have a user form that, based on the option button selected, a value needs
to be returned from a different cell in a separate worksheet. I get an
out
of range error when I try the following code:

Sheets("Sample Selection").Select
ActiveSheet.Unprotect
Cells(2, 5) = Worksheets("SampleCalc").Cells(3, 45)

What am I doing wrong?

--
JP