Consolidate rows from multiple worksheets in one based on cell criteria

I am learning vba but I am running into a difficult challenge. I have been reading several threads but I am not reaching the desired output. Hope one of you can help

I have a workbook with multiple worksheets. The layout of these worksheets are all the same.
Every week I need to report all actions that have been completed. These actions originate from the different worksheets with the names

PRIORITY 1, PRIORITY 2, PRIORITY 3, PRIORITY4

If a cell in column G in any of the rows and in any of the worksheets previoisly mentioned has the value CLOSED or CANCELLED it should copy the entire row to the next available row in worksheet CLOSED.

Some additional request are: Keep the first row of the sheet CLOSED the same but clear the rest of the sheet CLOSED at the each run of the macro to prevent double entries

I have tried to combine several threads But I am too new at VBA to get it working. Hope sombody is up to the challenge

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Thanks for the tip and also thanks for the reply with the code. I have posted the dummy file to clarify. The posted vba gives an error on the

" .autofilter 1,"Closed", xlor "Cancelled" "

Could be because I'working with Excel 2003.

Hope this helps

Thanks

Honestly I tried,

I can't get past the error. The code is not working. I was able to create this

Sub CopyRows()
Dim LR As Long, i As Long, j As Long
With Sheets("PRIORITY 4")
LR = .Range("G" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If .Range("G" & i).Value = "Closed" Or .Range("G" & i).Value = "Cancelled" Then
j = j + 1
.Rows(i).Copy Destination:=Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End With
MsgBox "Done:" & vbNewLine & j & " rows copied", vbInformation
End Sub

This code was borrowed changed as far as i was able to understand and it copies the lines from ones sheet. Can sombody help me and let it look at the other sheets as well, but make it as the suggested code from snb to exlude in stead of include worksheets to look at.
And to ensure that the sheet is always up to date to clear the sheet but not the headers.

Thanks

The code works briliant. there is one that would work nice in future. Could the code use an exlusion of sheets in stead of an array. SNB used

if sh.name<>"closed" then .....

.

If a Priority 5 sheet would be added the code does not need updating.

For now thanks a lot!

Yes that's working perfect. Thanks a million

I do wish to know what is happening exactly.

Most of it I can read, but there is one item that I can not find as a variable. The number of columns that will be copied. Does it take into account that some column are hidden?

Thanks a lot again