VBA to copy all rows in any sheet with 1 in column A and copy entire row to specific sheet

Option Explicit

Sub TakeOff()
    Dim ws As Worksheet, i As Long
    Dim sh As Worksheet, lr2 As Long
    Set sh = Sheets("Take off")
    Dim lr As Long
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            If ws.Name <> "Take off" Then
                lr = ws.Range("C" & Rows.Count).End(xlUp).Row
                For i = 1 To lr
                    lr2 = sh.Range("C" & Rows.Count).End(xlUp).Row
                    If ws.Range("A" & i) = 1 Then
                        ws.Range("B" & i & ":U" & i).Copy
                        sh.Range("B" & lr2 + 1).PasteSpecial xlPasteValues
                    End If
                Next i
            End If
        End If
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Completed"

End Sub

Add this line of code directly below the Application.Screenupdating = False

sh.Range("C2").CurrentRegion.ClearContents

See if that does what you want.

try this on for size:

Option Explicit

Sub TakeOff()
    Dim ws As Worksheet, i As Long
    Dim sh As Worksheet, lr2 As Long
    Set sh = Sheets("Take off")
    Dim lr As Long
    lr2 = sh.Range("C" & Rows.Count).End(xlUp).Row
    sh.Range("B2:U" & lr2).ClearContents
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            If ws.Name <> "Take off" Then
                lr = ws.Range("C" & Rows.Count).End(xlUp).Row
                For i = 1 To lr
                    lr2 = sh.Range("C" & Rows.Count).End(xlUp).Row
                    If ws.Range("A" & i) = 1 Then
                        ws.Range("B" & i & ":U" & i).Copy
                        sh.Range("B" & lr2 + 1).PasteSpecial xlPasteValues
                    End If
                Next i
            End If
        End If
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Completed"

End Sub

Is your actual file laid out exactly the same as the file you uploaded because the code presented starts to delete at Row 2 which is the first line of data in your workbook. In the meantime, change this line to fix the final formula and keep it in place.

sh.Range("B2:U" & lr2).ClearContents

to

sh.Range("B2:U" & lr2-1).ClearContents

I have attached your file that runs and keeps the formula in line 48. Code is embedded in the file.