Your CommandButton1 code could be changed to:
Sheets("Project").UsedRange.Offset(1).ClearContents '<------If you mean to clear the Project sheet
I think that the formula in A2 should be without double quotes, like
IF(SUM(A3:A7) =0,0,1)
I assume you want the result to be numbers, not text.
Does this come close to what you had in mind? Try it on a copy of your workbook first.
Sub Maybe()
Dim pjWs As Worksheet, ws As Worksheet, i As Long, lr As Long
Set pjWs = Worksheets("Project")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Project" Then
With ws
lr = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 2 To .Cells(.Rows.Count, 2).End(xlUp).Row - 6
If .Cells(i, 1).Value > 0 Then pjWs.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 4).Value = .Cells(i, 1).Resize(, 4).Value
Next i
If .Cells(lr - 4, 1).Value > 0 Then
pjWs.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(6, 4).Value = .Cells(lr - 5, 1).Resize(6, 4).Value
End If
End With
End If
Next ws
End Sub
In your code, you could change the last row line to
ws.Cells(ws.Rows.Count, "A").End(xlUp).Row - 4 '<---- Add the - 4
That way you won't have a problem with "Labour"
I used Column B to find the last used row. Just in case someone puts something below the "Labor Cost" cell.
Any particular reason that you use Labour and Labor?