So all your sheets are projects with the project name in A1 and header in row 2.
For the code it doesn't matter how many sheets you have, so you can add as many as you want.
I named the result sheet Overview. If you want a different name then don't forget to alter the code (best done by Ctrl+H; find and replace).
Before running the code create the Overview sheet.
On the Overview the project names are placed in column E and header in row 1.
When the code is run:
- The Overview sheet is cleared, except for it's header in row 1.
- All sheets except the Overview sheet are scanned for "High Priority" and "Low Priority" in column A. When found placed in the Overview sheet.
Here is the code:
Dim ws As Worksheet
Dim lRow As Long
Sheets("Overview").Range(Cells(2, "A"), Cells(Rows.Count, "E")).ClearContents
For Each ws In Worksheets
If ws.Name = "Overview" Then GoTo Skip
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A3:A" & lRow)
If cell = "High Priority" Or cell = "Low Priority" Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Copy
Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Range("A1").Copy Sheets("Overview").Range("E" & Rows.Count).End(xlUp).Offset(1, 0)