Option Explicit
Sub suchen_kopieren_einfügen()
Dim strSuchwort As String
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim i As Integer, Z1 As Integer, LR As Integer
Dim NZ As Integer, SP As Integer
Set wks1 = Worksheets("Importliste")
Z1 = 2
SP = 5
With wks1
LR = .Cells(.Rows.Count, SP).End(xlUp).Row
For i = Z1 To LR
strSuchwort = Replace(.Cells(i, SP), " ", "_")
If IsError(Evaluate(strSuchwort & "!A1")) Then
MsgBox "Blatt: '" & strSuchwort & "' existiert nicht"
Exit For
Else
Set wks2 = Sheets(strSuchwort)
NZ = wks2.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
.Rows(i).Copy wks2.Rows(NZ)
End If
Next
End With
End Sub