ExcelMacro.vba

Sub GenerateSummary()

    'Keep track of current sheet
    Dim ws As Worksheet

    'Keep a reference to the Summary sheet
    Dim summarySheet As Worksheet
    summarySheet = Sheets("Summary")

    'Disable screen updates to make this run faster
    Application.ScreenUpdating = False

    'Activate the summary sheet
    summarySheet.Activate

        'Loop over every sheet  
        For Each ws In Worksheets
        
            'Ignore the summary sheet
            If ws.Name <> "Summary" Then

                'Loop over every row in the sheet
                For i = 1 to ws.UsedRange.Rows.Count
                    cellProjectNum = ws.Cells(i, 4)
                    cellProjectComments = ws.Cells(i, 5)

                    'If both target cells are blank
                    If cellProjectNum.Value = "" And cellProjectComments.Value = ""

                        'Find the next empty row of the summary sheet
                        summarySheet.UsedRange 'Refresh UsedRange
                        lastRow = summarySheet.UsedRange.Rows(summarySheet.UsedRange.Rows.Count).Row + 1

                        'Copy the row to the Summary sheet
                        ws.Rows(i).EntireRow.Copy summarySheet.Range("A" & lastRow)
                Next
            End If
        Next ws
    End With

    'Re-enable screen updating
    Application.ScreenUpdating = True
     
End Sub