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

--
caoimhincrya
-----------------------------------------------------------------------
caoimhincryan's Profile: http://www.excelforum.com/member.php...fo&userid=2560
View this thread: http://www.excelforum.com/showthread.php?threadid=39086

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

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

Can anyone help?


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