AW: Was hälst du davon....
13.11.2018 21:24:13
Werner
Hallo Tim,
teste mal:
Option Explicit
Public Sub Suchen_kopieren()
Dim ws As Worksheet, loLetzte As Long, loLetzteZ As Long
Dim strSuche As String
strSuche = Worksheets("kombination").Range("B2")
If strSuche = vbNullString Then
MsgBox "Sie haben keinen Suchbegriff eingegeben."
Exit Sub
End If
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "2000E", "4000F", "8000H"
With ws
If WorksheetFunction.CountIf(.Columns(1), strSuche) > 0 Then
.UsedRange.AutoFilter Field:=1, Criteria1:=strSuche
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1).Copy
With Worksheets("kombination")
loLetzteZ = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
.Cells(loLetzteZ, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
.AutoFilterMode = False
End If
End With
Case Else
'nix machen
End Select
Next ws
End Sub
Gruß Werner