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

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

Hi Howard,

Am Fri, 18 Nov 2016 08:09:30 +0100 schrieb Claus Busch:

your drop down in Sheet1 cell A1. Then try:

sorry, I didn't read carefully.

Try:

Sub Test()
Dim varSh As Variant
Dim LRow As Long, i As Long
Dim myRng As Range
Dim myStr As String

varSh = Split(Sheets("Sheet1").Range("A1").Validation.Form ula1, ";")

For i = LBound(varSh) To UBound(varSh)
With Sheets(varSh(i))
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
Set myRng = .Range("D5:D" & LRow)
'Do stuff
End With
Next
End Sub

Check out what separator you have in the validation list. In german
system it is a semicolon. Perhaps it is a comma in your system.


Regards
Claus B.
--
Windows10
Office 2016

On Friday, November 18, 2016 at 12:15:13 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 18 Nov 2016 08:09:30 +0100 schrieb Claus Busch:

your drop down in Sheet1 cell A1. Then try:

sorry, I didn't read carefully.

Try:

Sub Test()
Dim varSh As Variant
Dim LRow As Long, i As Long
Dim myRng As Range
Dim myStr As String

varSh = Split(Sheets("Sheet1").Range("A1").Validation.Form ula1, ";")

For i = LBound(varSh) To UBound(varSh)
With Sheets(varSh(i))
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
Set myRng = .Range("D5:D" & LRow)
'Do stuff
End With
Next
End Sub

Check out what separator you have in the validation list. In german
system it is a semicolon. Perhaps it is a comma in your system.


Regards
Claus B.


Hi Claus.

Actually, the first one is correct for what I want to do. I like the second for a good example of "for all sheets in drop down".
Thank you very much.

Howard

Chúc bạn đắt hÃ*ng
_________________
bulong móng

https://t.co/1OiBxEhCD2

hay quá đúng cái em đang cần đánh dấu mai em call bác
_________________
kinky videos

https://t.co/Q7LrbOZYf0