Set a range on a variable sheet?
 

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

Set a range on a variable sheet?
 

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:D" & 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

On 14 Dec., 16:27, Noob McKnownowt
wrote:
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

Set a range on a variable sheet?
 

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

cheers pal, works a treat

The Noob

"Per Jessen" wrote:

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:D" & 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

On 14 Dec., 16:27, Noob McKnownowt
wrote:
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