Sub Сбор()
Dim Sht As Worksheet
Dim iLastRow As Long
    For Each Sht In Worksheets
      If Sht.Name <> "Сводка" Then        ' кроме листа "общий"
        With Sht
          iLastRow = Cells(Rows.Count, 4).End(xlUp).Row + 1
           .Range("A7").Copy Cells(iLastRow, 4)
           .Range("C20").Copy Cells(iLastRow, 5)
           .Range("H5").Copy Cells(iLastRow, 6)
           .Range("I20").Copy Cells(iLastRow, 7)
            Cells(iLastRow, 3) = Sht.Name
        End With
      End If
    Next
End Sub