Hi Howard,

Am Thu, 17 Nov 2016 22:52:09 -0800 (PST) schrieb L. Howard:

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.

your drop down in Sheet1 cell A1. Then try:

Sub Test()
Dim shN As String
Dim LRow As Long
Dim myRng As Range

shN = Sheets("Sheet1").Range("A1")
With Sheets(shN)
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
Set myRng = .Range("D5:D" & LRow)
'Do stuff
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016