Is there a way to set a named range, based on the active sheet?

Example:
Range1 =LOOKUP(2,1/('Sheet1'!$B$1:$B$5000<""),ROW('Sheet1'!$B$1:$B$5 000))

I want to be able to set this named ranged, based on the sheet I am in. If
on sheet 2 it would set the range as:
Range1 =LOOKUP(2,1/('Sheet2'!$B$1:$B$5000<""),ROW('Sheet2'!$B$1:$B$5 000))

etc...