Good morning everyone.

I have a number of worksheets all in the same workbook containing five or
six columns of data.

Is there a way of searching all of the worksheets for a value I enter
(perhaps on a seperate worksheet) so that the entire row(s) that contain that
value are returned?


Is this possible?




--
Many thanks

Mistysweep

Is this what you had in mind?
On "Sheet4" cell "a2" put what you want to look for.
The macro will loop through each sheet, highlighting the row the search
string was found on and put the row number and sheet name on Sheet 4
columns 2 and 3 respectively


Sub FindSomething()

Dim intSheetCounter As Integer
Dim strFoundrow As String
Dim intPlaceRow As Integer

intPlaceRow = 2
For intSheetCounter = 1 To Worksheets.Count
Worksheets(intSheetCounter).Select
cells(1,1).select
Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells (2, 1).Value,
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
False).EntireRow.Select
strFoundrow =
Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells (2, 1).Value,
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
False).Row
ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRo w, 3) =
Worksheets(intSheetCounter).Name
ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRo w, 2) =
strFoundrow
intPlaceRow = intPlaceRow + 1
Next
End Sub


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=542057

Tried it and keep getting a compile error - syntax error at :-

Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells (2,1).Value.

Any ideas?

--
Many thanks

Mistysweep


"bgeier" wrote:


Is this what you had in mind?
On "Sheet4" cell "a2" put what you want to look for.
The macro will loop through each sheet, highlighting the row the search
string was found on and put the row number and sheet name on Sheet 4
columns 2 and 3 respectively


Sub FindSomething()

Dim intSheetCounter As Integer
Dim strFoundrow As String
Dim intPlaceRow As Integer

intPlaceRow = 2
For intSheetCounter = 1 To Worksheets.Count
Worksheets(intSheetCounter).Select
cells(1,1).select
Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells (2, 1).Value,
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
False).EntireRow.Select
strFoundrow =
Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells (2, 1).Value,
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
False).Row
ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRo w, 3) =
Worksheets(intSheetCounter).Name
ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRo w, 2) =
strFoundrow
intPlaceRow = intPlaceRow + 1
Next
End Sub


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=542057

Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells (2, 1).Value,
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
False).EntireRow.Select

Check you have commas after
Value
ActiveCell
xlFormulas
xlWhole
xlByColumns
xlNext
False

I tested it on my machine again and it worked


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=542057