Set a range on a variable sheet?

[Noob McKnownowt]

Hey guys, me again

I am having a problem, below i have written some code but cant figure one
particular bit out.

I am searching through multiple sheets for a match to user input, the
problem i am having is i am unable to set the range to be seached over
multiple sheets. its always the same column, but .usedrange doesn't seem to
work for me in this instance.

The line i am having problems with i have highlighted with *** either side
of the code.

Sub SearchDesc()

Dim Cell As Range
Dim Rng As Range
Dim WrkSht As Worksheet
Dim myText As String

myText = Sheets("Summary").TextBoxDesc.Value
***Set Rng = Sheets(Variable sheet name Here).Range("D")***

If myText = "" Then Exit Sub

For Each WrkSht In ThisWorkbook.Worksheets
If WrkSht.Name = "sheet1" Or WrkSht.Name = "sheet2" Then GoTo myNext
For Each Cell In Rng
If myText = Cell.Value Then
Cell.EntireRow.Copy
Sheets("sheet name").Range("A65536").End(xlUp).Offset(1,
0).PasteSpecial
End If
Next Cell

myNext:

Next WrkSht

End Sub

Any assistance would be very much apprieciated.

Cheers Lads.

The Noob

 

[Per Jessen]

Hi

Set the Rng variable within the WrkSht loop:

Sub SearchDesc()
Dim Cell As Range
Dim Rng As Range
Dim WrkSht As Worksheet
Dim myText As String
Dim LastRow As Long

myText = Sheets("Summary").TextBoxDesc.Value

If myText = "" Then Exit Sub

For Each WrkSht In ThisWorkbook.Worksheets
If WrkSht.Name = "Sheet1" Or WrkSht.Name = "Sheet2" Then GoTo
myNext
LastRow = Sheets(WrkSht.Name).Range("D" & Rows.Count).End
(xlUp).Row
Set Rng = Worksheets(WrkSht.Name).Range("D1" & LastRow)
For Each Cell In Rng
If myText = Cell.Value Then
Cell.EntireRow.Copy
Sheets("sheet name").Range("A65536").End(xlUp).Offset
(1, 0).PasteSpecial
End If
Next Cell

myNext:
Next WrkSht
End Sub

Regards,
Per

 

[Noob McKnownowt]

OOHHH, nice, i would never have thought of that, yeah i feel stupid incase
you were wondering

cheers pal, works a treat

The Noob