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: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