cell value from different worksheet

[JPCPA]

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?

 

[Dave Peterson]

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

 

[Nigel]

Your code should work but no need to select

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

 

[JPCPA]

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.