Copy and Find macro help please.

[adelphus]

I have a workbook to track sales and commissions. In this workbook there are separate worksheets for each salesperson, ( Alan, Dan, Jim, Walter, Roseanne). Each worksheet is formatted the same with headers for date (col A), po# (col B), status (col C), customer (col D), manufacturer (col E), and sales amount (col F). Under status in column C is listed either open or paid, depending on if the sale is completed or not. At the end of each month I would like to run a macro to search for all the open listings from column C in each of the 5 worksheets begining in the range c2:c200 and then copy the entire row where an open exists, a2:F2 for example, to another worksheet called open accounts in the same workbook. I believe this is possible, just beyond my abilities. Any pointers please?

[funkymonkUK]

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 Sub

[adelphus]

Thanks...I entered this and created a sheet called all open sales but when I try to run I get the debugger with a compile error "sub or function not defined" and the line "lastrowpub" highlighted. Any ideas?

[funkymonkUK]

sorry forgot to include this part

paste this at the top of your code so this should be above the part that starts off with sub test

this functions works out the last row of a specific sheet

Function lastRowpub(colnum As Long, Optional sh As Worksheet) As Long
' Count Rows in table
If sh Is Nothing Then Set sh = ActiveSheet
lastRowpub = sh.Cells(sh.Rows.Count, colnum).End(xlUp).Row
End Function