List Worksheets in seperate worksheet

[caoimhincryan]

Can anyone help me with this problem. I have a workbook based on th
attached workbook. It uses a macro to find data in worksheets with a
'x' in a column. What I need to do is , that when the the macro i
clicked, it puts which sheet the data came from in column E in th
special order worksheet.

Can anyone help

+-------------------------------------------------------------------
|Filename: CheckSheetsOrders.zip
|Download: http://www.excelforum.com/attachment.php?postid=3651
+-------------------------------------------------------------------

 

[Gareth]

Try something like this:

Sub FIndXs()

Dim shReport As Worksheet, shRead As Worksheet
Dim lrow As Long, lwrite As Long

'Assume activesheet is teh report sheet
'(it will be if the code is run from a button on it)
Set shReport = ActiveSheet
lwrite = 2

'go thru each worksheet
For Each shRead In ActiveWorkbook.Sheets
'skip our report sheet
If Not shRead Is shReport Then
'go thru each row in the worksheet until we find an X
With shRead
For lrow = 1 To .UsedRange.Rows.Count
If LCase(.Cells(lrow, 3)) Like "*x*" Then
'write this data to our report sheet
Range(shReport.Cells(lwrite, 1), _
shReport.Cells(lwrite, 4)).Value = _
Range(.Cells(lrow, 1), .Cells(lrow, 4)).Value
'write name of source sheet
shReport.Cells(lwrite, 5) = shRead.Name
lwrite = lwrite + 1
End If
Next lrow
End With
End If
Next shRead

End Sub