I have several worksheets that have purchase order data on them:

PO Num Order Date Vendor Status

I have one worksheet called "Paid Invoices".

Status is changed from "Open" to "Paid" based on a PO match. What I would
like to do is to create an Accrual worksheet that searches through the PO
Data worksheets for anything that's still "Open" and returns that row's
information to the Accrual worksheet.

Thanks!

Try this
'Read through the comments marked with '
Sub CopyOpenPO()
'Workbook must contain a Summary sheet named Accrual

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim ws As Worksheet
Dim LastRow As Integer

'j contains the index of the column which contains Open status
'j=1 implies column A
j = 4 'Assuming status is in Col D

'Assuming first row on Accrual sheet has header information
k = 2

'Loop through all sheets
For Each ws In Worksheets
i = 1
ws.Activate
'Process if worksheet name is not in (Accrual,Paid Invoices)
If WorksheetFunction.And(ws.Name < "Accrual", ws.Name < "Paid Invoices")
Then
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
ws.Activate
If Cells(i, j) = "Open" Then
Cells(i, 1).EntireRow.Copy
Sheets("Accrual").Activate
Cells(k, 1).Select
ActiveSheet.Paste
k = k + 1
End If
Next i
End If
Next ws
MsgBox "Process Complete"
End Sub

"Dave" wrote:

I have several worksheets that have purchase order data on them:

PO Num Order Date Vendor Status

I have one worksheet called "Paid Invoices".

Status is changed from "Open" to "Paid" based on a PO match. What I would
like to do is to create an Accrual worksheet that searches through the PO
Data worksheets for anything that's still "Open" and returns that row's
information to the Accrual worksheet.

Thanks!

This is working PERFECTLY!!!!

Thank you SO MUCH!!!

"Sheeloo" wrote:

Try this
'Read through the comments marked with '
Sub CopyOpenPO()
'Workbook must contain a Summary sheet named Accrual

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim ws As Worksheet
Dim LastRow As Integer

'j contains the index of the column which contains Open status
'j=1 implies column A
j = 4 'Assuming status is in Col D

'Assuming first row on Accrual sheet has header information
k = 2

'Loop through all sheets
For Each ws In Worksheets
i = 1
ws.Activate
'Process if worksheet name is not in (Accrual,Paid Invoices)
If WorksheetFunction.And(ws.Name < "Accrual", ws.Name < "Paid Invoices")
Then
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
ws.Activate
If Cells(i, j) = "Open" Then
Cells(i, 1).EntireRow.Copy
Sheets("Accrual").Activate
Cells(k, 1).Select
ActiveSheet.Paste
k = k + 1
End If
Next i
End If
Next ws
MsgBox "Process Complete"
End Sub

"Dave" wrote:

I have several worksheets that have purchase order data on them:

PO Num Order Date Vendor Status

I have one worksheet called "Paid Invoices".

Status is changed from "Open" to "Paid" based on a PO match. What I would
like to do is to create an Accrual worksheet that searches through the PO
Data worksheets for anything that's still "Open" and returns that row's
information to the Accrual worksheet.

Thanks!