Copy and Find macro help please.

[adelphus]

I have a workbook to track sales and commissions. In this workboo
there are separate worksheets for each salesperson, ( Alan, Dan, Jim
Walter, Roseanne). Each worksheet is formatted the same with header
for date (col A), po# (col B), status (col C), customer (col D)
manufacturer (col E), and sales amount (col F). Under status in colum
C is listed either open or paid, depending on if the sale is complete
or not. At the end of each month I would like to run a macro to searc
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 a
open exists, a2:F2 for example, to another worksheet called ope
accounts in the same workbook. I believe this is possible, just beyon
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 Su

 

[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