Option Explicit
Public Sub test()
Const MY_NUMBER As Long = 1234
Dim avntSource() As Variant, avntTarget() As Variant
Dim ialngCount As Long, ialngRow As Long
avntSource() = Tabelle1.UsedRange.Value
For ialngRow = 1 To UBound(avntSource)
If avntSource(ialngRow, 2) = MY_NUMBER Then
ReDim Preserve avntTarget(1, ialngCount) As Variant
avntTarget(0, ialngCount) = avntSource(ialngRow, 1)
avntTarget(1, ialngCount) = avntSource(ialngRow, 2)
ialngCount = ialngCount + 1
End If
Next
If ialngCount = 0 Then
Call MsgBox(Prompt:="Die Zahl wurde nicht gefunden..!", _
Buttons:=vbExclamation, Title:="Datensuche")
Else
With Tabelle2
Call .UsedRange.ClearContents
.Range(.Cells(1, 1), .Cells(UBound(avntTarget, 2) + 1, 2)).Value = _
WorksheetFunction.Transpose(avntTarget())
End With
End If
End Sub