Option Explicit
Public Sub Kopieren()
Dim ws As Worksheet, loLetzte As Long, loLetzteZiel As Long
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Summe", "Tabelle5"
Case Else
With ws
If WorksheetFunction.CountA(.Columns(1)) > 0 Then
loLetzte = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(.Cells(1, "A"), .Cells(loLetzte, "A")).SpecialCells(xlCellTypeConstants).Copy
With Worksheets("Summe")
loLetzteZiel = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Row
If .Cells(1, "A") = "" Then loLetzteZiel = 1
.Cells(loLetzteZiel, "A").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End If
End With
End Select
Next ws
End Sub