if you have a workbook with five sheets as follows

sheet1 = all open sales
sheet2 = "Alan"
sheet3 = "Dan"
Sheet4 = "Jim"
Sheet5="Walter"

You create a loop


Sub test()

x = lastRowpub(1, Worksheets("Sheet1"))

For i = Worksheets(2) To Worksheets.Count

For Each cell In Range("c2:c200")
If cell.Value = "Open" Then
cell.EntireRow.Copy
Worksheets("sheet1").Range("a" & x).PasteSpecial Paste:=xlValues
x = x + 1
End If
Next
Next

End Su

--
funkymonkU
-----------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...fo&userid=1813
View this thread: http://www.excelforum.com/showthread.php?threadid=55598