With sheet names in a drop down in Cell A2 what is the generic

With Sheets("Sheet name in Drop Down")
'Do stuff
End With

and

Set Rng = Sheets("Sheet name in Drop Down").Range("D5", Range("D5").End(xlDown))


I want to Find a String on any one of the sheets in the drop down and return an offset value to main sheet.

I can write the Find but am not sure how to ref the sheet from the drop down as the search sheet.

Thanks,
Howard