Mit schön­er Regelmäßigkeit kommt in Foren die Anfrage nach diesem Prob­lem: Es soll in ein­er Spalte ein bes­timmter Wert gefun­den und bei Erfolg die Dat­en der Zeile in ein anderes Blatt kopiert wer­den. Der Lösungsmöglichkeit­en gibt es diverse, teils ein­fache, teils anspruchsvolle. Einige davon wer­den wir Ihnen hier vorstellen. Mitunter mit nur eini­gen Hin­weisen, öfter mit VBA-Code.

Die Datenbasis

Gewiss wollen Sie das, was hier beschrieben wird, auch nachvol­lziehen kön­nen. Dazu ist es aus­ge­sprochen hil­fre­ich, wenn Sie mit den gle­ichen Dat­en arbeit­en wie wir. Darum laden Sie gle­ich diese Arbeitsmappe herunter. Es ist eine Liste der Abge­ord­neten des Deutschen Bun­destages, Stand Mitte 2015. Etwas Arbeit soll­ten Sie jedoch im Vor­wege investieren:

• Benen­nen Sie das Tabel­len­blatt um in Suchen&Kopieren
• Wenn Sie es vorziehen, kön­nen Sie auch eine neue erste Zeile ein­fü­gen und mit Über­schriften verse­hen
• Und bei der Gele­gen­heit fügen Sie auch gle­ich ein neues, leeres Tabel­len­blatt hinzu, welch­es den Namen Ziel bekom­men sollte.

▲ nach oben …

Ohne Makro/VBA

Die ein­fach­ste Lösung, welche wir Ihnen hier vorstellen ist in erster Lin­ie ein ganz nor­maler Fil­ter. Sie lassen sich per Aut­oFil­ter oder in ein­er Intel­li­gen­ten Tabelle nur den oder die Namen anzeigen, welche rel­e­vant sind. Diese ganzen Zeilen oder nur die Bere­iche mit Dat­en markieren, StrgC zum kopieren; dann wech­seln Sie in die Ziel – Tabelle und fügen beispiel­sweise per StrgV den Inhalt der Zwis­chen­ablage an der gewün­scht­en Posi­tion ein. Anschließend schal­ten Sie den Fil­ter wieder aus und Sie kön­nen eine neue Suche begin­nen.

Wenn Sie diesen Vor­gang öfter ein­mal durch­führen müssen, dann ist vielle­icht auch der Spezial­fil­ter für Sie von Inter­esse. Sie find­en diesen im Menü Dat­en | Sortieren und Fil­tern | Erweit­ert. Er mag anfangs etwas unge­wohnt sein, aber nach ein­er kurzen Eingewöh­nung ist das Arbeit­en damit recht kom­fort­a­bel.

So richtig empfehlen mögen wir es nicht, aber es geht natür­lich auch über die Suchen-Funk­tion­al­ität des Excel. Das ist aber recht umständlich, ins­beson­dere dann, wenn der zu find­ende Term mehrfach in der Auflis­tung vorhan­den ist.

▲ nach oben …

Mit Makro / VBA

Im Prinzip (und aus unser­er Sicht sowieso) ist dieses die ele­gan­tere Lösung des Prob­lems. Sie kön­nen rasch einen Such­be­griff eingeben und „wie von Geis­ter­hand” erledigt Excel den Rest für Sie. Die einzelne oder auch mehrere Fund­stellen wer­den automa­tisch in das Ziel – Blatt kopiert, dort natür­lich automa­tisch an das Ende der Liste ange­fügt. Und da ist mit rel­a­tiv wenig Aufwand auch einiges an Vari­a­tio­nen mach­bar.

In diesem ersten Beitrag beschränken wir uns auf eine sehr sim­ple Form des kopierens. Kleine Vari­a­tio­nen sind hier ange­sprochen, unter­schiedliche und aufwendi­gere Vorgänge wer­den in Fol­ge­beiträ­gen besprochen. Die Daten­ba­sis ist ‑zumin­d­est jet­zt noch- die weit­er oben ange­sproch­ene Abge­ord­neten-Liste. Sie eignet sich sehr schön, weil über 600 Daten­sätze schon mehr als nur „Spielkram” sind. 😉 

Hin­weis: Grund­sät­zlich stellen wir Ihnen zu diesem The­ma keine fer­ti­gen *.xlsm-Dateien zur Ver­fü­gung. Sie find­en den Quell­code in eini­gen Fällen direkt hier im Beitrag geschrieben, in jedem Fall aber als gepack­te Datei zum Down­load. Wie Sie den Code in eine Excel-Mappe ein­binden kön­nen, das kön­nen Sie hier nach­le­sen. – Wenn nichts anderes ver­merkt ist, kön­nen Sie den Code in das Mod­ul des Arbeits­blatts, wo die Quell-Dat­en drin ste­hen (hier: Suchen&Kopieren) oder in das Mod­ul DieseAr­beitsmappe ein­fü­gen. Das gilt aber nur für dieses The­ma, keineswegs für andere Beiträge dieses Blogs.

Die „Grundausstattung”  😉 

Hier nun der Beispiel-Code, den Sie an dieser Stelle als gepack­te Text­datei herunter laden kön­nen:

Option Explicit

Sub FindAndCopy1()
   Dim rngSuch As Range, wksSrc As WorkSheet, wksDst As WorkSheet
   Dim strSuch As String, rngFound As Range
   Dim strFirst As String, FoundAdr As String
   Dim ZeSrc As Integer, ZeDst As Integer, lRow As Long, lRowDst As Long
  
   Set wksSrc = Tabelle1   'Codename!
   Set wksDst = Tabelle2   'Nicht der Blattname (der leicht umbenannt werden kann)
   lRow = wksSrc.Cells(Rows.Count, 1).End(xlUp).Row
   Set rngSuch = wksSrc.Range("A2:B" & lRow) 'Name oder Vorname suchen
   With wksDst
      lRowDst = WorksheetFunction.Max(2, .Cells(Rows.Count, 1).End(xlUp).Row)
      wksDst.Range("A2:B" & lRowDst).EntireRow.Delete 'Alle Namen-Einträge löschen
      If .Range("A1") = "" Then  'Wichtig, damit in A1 etwas steht (eventuell anpassen)
         .Cells(1, 1) = "Name"
         .Cells(1, 2) = "Vorname"
         .Cells(1, 3) = "Fraktion"
      End If
   End With
   strSuch = InputBox("Bitte das Suchwort eingeben", "Filter")
   With rngSuch
      Set rngFound = .Find(what:=strSuch)
      If Not rngFound Is Nothing Then
         strFirst = rngFound.Address
         Do
            FoundAdr = rngFound.Address
            ZeSrc = rngFound.Row
            ZeDst = wksDst.Cells(Rows.Count, 1).End(xlUp).Row + 1
            wksSrc.Range("A" & ZeSrc & ":B" & ZeSrc).Copy wksDst.Cells(ZeDst, 1) 'Zeile von Spalte A bis B kopieren
            Set rngFound = .FindNext(rngFound)
         Loop While Not rngFound Is Nothing And rngFound.Address <> strFirst
      Else
         MsgBox "Der Name  '" & strSuch & "'  wurde nicht gefunden!", vbInformation, "Fehleingebe?"
      End If
   End With
End Sub

Der Code ist recht ein­fach gehal­ten, darum auch ohne Kom­mentare. Pro­bieren Sie ihn ein­fach ein­mal aus; beacht­en Sie aber, dass hier in jedem Fall das Arbeits­blatt Ziel existieren muss und auch der Name des Sheets mit den Quell­dat­en muss Suchen&Kopieren sein, wenn Sie den Code nicht entsprechend angepasst haben.

▲ nach oben …

Genauigkeit ist Trumpf

Spätestens jet­zt soll­ten Sie ein­mal einen Namen eingeben, der garantiert nicht in der Liste existiert. Beispiel­sweise „xyz”. Dann erken­nen Sie auch, dass das Pro­gramm Sie darauf aufmerk­sam macht und naturgemäß keine Dat­en überträgt. Aber das mit dem „naturgemäß” ist so eine Sache … 

Geben Sie in die Input­Box doch ein­fach ein­mal bar ein, Groß- oder Klein­schrei­bung ist egal. Dann klin­gelt das Tele­fon und ganz in Gedanken Klick­en Sie auf OK oder drück­en die Taste Eingabe. Und es kommt keine Fehler­mel­dung, was Sie vielle­icht über­rascht, schließlich gibt es keine/n Abge­ord­neten mit dem Namen. Noch größer wird vielle­icht die Über­raschung, wenn Sie sich das Blatt Ziel anse­hen. Ein Dutzend Namen!

Rasch wer­den Sie erken­nen, dass das Pro­gramm alle Daten­sätze her­aus­ge­sucht hat, welche die Buch­stabenkom­bi­na­tion des Such­be­griffs im Namen enthal­ten. Egal ob zu Beginn oder irgend­wo im Namen. Das kann in manchen Fällen ein Vorteil sein, hier ist es eher von Nachteil, denn es wäre unter Umstän­den einiges an Nachar­beit fäl­lig, wenn nur die exak­ten Begriffe hät­ten kopiert wer­den sollen.

Eine wirk­lich kleine Änderung des Codes bringt Abhil­fe:

Set rngFound = .Find(what:=strSuch)
Set rngFound = .Find(what:=strSuch, LookAt:=xlWhole)

Sie ergänzen also die Zeile um eine weit­ere Anweisung. Und wenn Sie jet­zt bar oder Bar eingeben, dann kommt wirk­lich die Fehler­mel­dung, dass der Name nicht existiert. Und damit Sie es nicht so schw­er haben  😛 , find­en Sie hier den fer­ti­gen Code.

▲ nach oben …

Joker sind möglich

Pro­bieren Sie den eben geän­derten Code doch ein­mal mit dem Namen Müller. Und Sie sehen, das klappt wun­der­bar. Zwei Abge­ord­nete wur­den gefun­den. Aber gemäß dem Mot­to, dass Ver­trauen gut ist, Kon­trolle aber bess­er schauen Sie noch ein­mal in die Quell­dat­en und suchen die Müllers. Hmmm, das sind ja eigentlich einige mehr. Es sind 7 Namen, wenn ich den Dop­pel­na­men (Müller-Gem­meke) dazu rechne.

Des Rät­sels Lösung: Geben Sie den Such­na­men mit einem Jok­er ein: Müller*. Der Stern ste­ht dabei für beliebig viele Zeichen die fol­gen kön­nen aber nicht müssen. Pro­bieren Sie es aus und der Erfolg zeigt sich sofort. Und das geht natür­lich auch, wenn Sie beispiel­sweise nur wis­sen, dass der Name mit La* begin­nt.

Falls Sie durch ver­schiedene Ver­suche etwas gen­ervt sind, weil Sie jedes Mal der besseren Kon­trolle wegen die Ergeb­nisse in der Tabelle Ziel löschen mussten, dann biete ich Ihnen einen kleinen Einzeil­er an, den Sie hier kopieren und an den vorhan­de­nen Code anfü­gen kön­nen. Anschließend bei Bedarf ein­fach :

Sub ErgebnisLoeschen()
   Sheets("Ziel").Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Delete
   'Achtung, wenn nur die Überschriften existieren, dann werden auch diese gelöscht
   'Orientieren Sie sich daher an dem Code weiter oben (lRowDst ... Max() ...)
End Sub

Ein­fach vor dem näch­sten Pro­belauf beispiel­sweise via AltF8 aufrufen und das Tabel­len­blatt Ziel ist wieder „sauber”. Denn nun kommt die näch­ste Her­aus­forderung: Sie wis­sen, dass der Name genau vier Zeichen hat und mit Po begin­nt. PO* geht nicht, da wür­den dann auch fünf- und mehrstel­lige Namen gefun­den wer­den. Pro­bieren Sie doch ein­fach ein­mal PO?? als Such­be­griff. Klappt! Zugegeben, dieses Namens­beispiel ist nicht der Hit. Aber im Bun­destag gibt es erstaunlich wenige „Meiers” aller Schreib­weisen. Son­st hät­ten Sie auf diese Weise nach „Meier”, „May­er”, „Maier”, „May­er” suchen kön­nen. Und „Mayr” oder „Meir”, … wer­den dann nicht gefun­den, denn das Frageze­ichen ste­ht für exakt 1 Zeichen. Ach ja, und wenn Sie nicht wis­sen, wie sich Frau oder Herr Meier­bohm, Maier­baum, Mayer­behr oder wie auch immer schreibt, dann würde die Eingabe M??erb* gewiss zum Ziel führen.

Hin­weis: Natür­lich funk­tion­iert das auch prinzip­iell mit Zahlen, beispiel­sweise Pro­dukt- oder Kun­den­num­mern. Mit den Jok­ern soll­ten Sie dabei aber bewusst umge­hen und das Ergeb­nis erforder­lichen­falls von Hand kor­rigieren.

▲ nach oben …

Nach­trag: Am 21.11.2017 erfol­gte eine Änderung des Codes „Grun­dausstat­tung”. Wichtig­ste Änderun­gen:

• Der Code kann jet­zt auch von dem zweit­en Arbeits­blatt aufgerufen wer­den (war vorher nicht möglich bzw. es erfol­gten keine Ein­träge)
• VBA-Bezug auf die Arbeits­blät­ter erfol­gt mit dem Code-Namen des jew­eili­gen Blattes, dadurch kann der Blat­tname verän­dert wer­den wie auch die Rei­hen­folge. Mehr zu diesem The­ma hier im Blog.
• Auf dem 2. Blatt bleibt die erste Zeile beste­hen, wenn A1 nicht leer ist.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 3,00 € freuen …