Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: VBA nur gefilterte Zellen kopieren

VBA nur gefilterte Zellen kopieren
17.01.2021 11:49:14
Romy
Guten Morgen
ich benötige ein bisschen Hilfestellung. Ich habe mir mittels Internet und diesem Forum folgenden Code zusammengebastelt:

Sub Pers1()
'Es werden alle vorhandenen Datensätze aus der Quelle in das Ziel kopiert
Dim wkQuelle As Worksheet, wkziel As Long
Dim cnt As Long, aktzeile As Long, erstzeile As Long
Dim LastRowAll As Long, LastColAll As Long
Set wkQuelle = Worksheets("LeitE")
Set wkziel = Worksheets("Telefonverzeichnis")
aktzeile = wkziel.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row  _
+ 2
erstzeile = wkziel.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious). _
Row + 3
With wkQuelle
If .FilterMode Then .ShowAllData
.ListObjects("KK_LeitE").Range.AutoFilter Field:=19, Criteria1:=""
For cnt = 1 To .Cells(Cells.Rows.Count, "A").End(xlUp).Row
wkziel.Cells(aktzeile, 1) = .Cells(cnt, 6)
wkziel.Cells(aktzeile, 2) = .Cells(cnt, 7)
wkziel.Cells(aktzeile, 3) = .Cells(cnt, 8) & ", " & .Cells(cnt, 9)
wkziel.Cells(aktzeile, 4) = .Cells(cnt, 19)
aktzeile = aktzeile + 1
Next
End With
LastRowAll = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColAll = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:= _
xlPrevious).Column
'Als Tabelle formatieren:
wkziel.ListObjects.Add(xlSrcRange, Range(Cells(erstzeile, 1), Cells(LastRowAll, LastColAll)), ,  _
xlYes).Name = "Tel_Pers1"
wkziel.ListObjects("Tel_Pers1").TableStyle = "TableStyleLight11"
Set wkziel = Nothing
Set wkQuelle = Nothing
End Sub
Durch den Autofilter für Spalte 19 soll die Quelltabelle nur diese Einträge anzeigen, welche in Spalte 19 einen Wert beinhalten. Leere Zeilen sollen ausgeblendet werden. In der weiteren Folge sollen auch nur die sichtbaren Zeilen kopiert werden. Und da liegt bei mir das Problem. Das vorliegende Makro kopiert auch die Zeilen/Zellen, welche in Spalte 19 keinen Wert haben. Könnt ihr mir bitte Hilfestellung geben?
Gruß Romy
Anzeige

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA nur gefilterte Zellen kopieren
17.01.2021 11:56:37
Daniel
Hi
Da musst du, bevor du die Werte überträgst, noch eine Abfrage einbauen, ob die Zeile sichtbar ist:
For cnt = 1 to ...
If .Rows(cnt).hidden = False then
wkziel.Cells(aktzeile, 1) = .Cells(cnt, 6)
Ende if
Next
Gruß Daniel
AW: o.w.T.
17.01.2021 12:06:03
Romy
Danke Daniel, funktioniert.
Anzeige
Wie wär's mit: If Not .Rows(cnt).Hidden Then ? owT
17.01.2021 14:24:35
Luc:-?
:-?
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

VBA zum Kopieren gefilterter Zellen in Excel


Schritt-für-Schritt-Anleitung

Um nur die gefilterten Zellen in Excel zu kopieren, kannst Du das folgende VBA-Skript verwenden. Dieses Skript stellt sicher, dass nur die sichtbaren (d.h. gefilterten) Zeilen in das Zielblatt kopiert werden.

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.
  2. Klicke auf Einfügen > Modul, um ein neues Modul zu erstellen.
  3. Füge den folgenden Code in das Modul ein:
Sub Pers1()
    Dim wkQuelle As Worksheet, wkziel As Worksheet
    Dim cnt As Long, aktzeile As Long, erstzeile As Long
    Dim LastRowAll As Long, LastColAll As Long

    Set wkQuelle = Worksheets("LeitE")
    Set wkziel = Worksheets("Telefonverzeichnis")

    aktzeile = wkziel.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2
    erstzeile = aktzeile + 1

    With wkQuelle
        If .FilterMode Then .ShowAllData
        .ListObjects("KK_LeitE").Range.AutoFilter Field:=19, Criteria1:=""

        For cnt = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If Not .Rows(cnt).Hidden Then
                wkziel.Cells(aktzeile, 1) = .Cells(cnt, 6)
                wkziel.Cells(aktzeile, 2) = .Cells(cnt, 7)
                wkziel.Cells(aktzeile, 3) = .Cells(cnt, 8) & ", " & .Cells(cnt, 9)
                wkziel.Cells(aktzeile, 4) = .Cells(cnt, 19)
                aktzeile = aktzeile + 1
            End If
        Next cnt
    End With

    LastRowAll = wkziel.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColAll = wkziel.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    wkziel.ListObjects.Add(xlSrcRange, Range(wkziel.Cells(erstzeile, 1), wkziel.Cells(LastRowAll, LastColAll)), , xlYes).Name = "Tel_Pers1"
    wkziel.ListObjects("Tel_Pers1").TableStyle = "TableStyleLight11"

    Set wkziel = Nothing
    Set wkQuelle = Nothing
End Sub
  1. Schließe den VBA-Editor und gehe zurück zu Excel.
  2. Führe das Makro aus, indem Du ALT + F8 drückst, das Makro auswählst und auf Ausführen klickst.

Häufige Fehler und Lösungen

  1. Problem: Das Makro kopiert auch leere Zeilen.

    • Lösung: Stelle sicher, dass die Bedingung If Not .Rows(cnt).Hidden Then korrekt implementiert ist, um nur sichtbare Zeilen zu kopieren.
  2. Problem: Fehler beim Ausführen des Skripts.

    • Lösung: Überprüfe, ob die Blattnamen ("LeitE" und "Telefonverzeichnis") korrekt sind und existieren.

Alternative Methoden

Wenn Du keine VBA-Makros nutzen möchtest, kannst Du auch die integrierten Excel-Funktionen verwenden. Wähle die gefilterten Daten manuell aus, kopiere sie mit STRG + C und füge sie mit STRG + V in das Zielblatt ein. Beachte, dass dies jedoch nicht so effizient ist wie die VBA-Lösung, besonders bei großen Datenmengen.


Praktische Beispiele

Angenommen, Du hast eine gefilterte Tabelle mit Kontaktdaten in "LeitE" und möchtest nur die gefilterten Zeilen in "Telefonverzeichnis" übertragen. Mit dem oben angegebenen Makro kannst Du nur die gefilterten Zellen kopieren und in die gewünschte Tabelle einfügen, ohne die leeren Zeilen zu berücksichtigen.


Tipps für Profis

  • Nutze die Funktion Application.ScreenUpdating = False am Anfang Deines Makros, um die Bildschirmaktualisierung während des Kopiervorgangs auszuschalten. Dies beschleunigt den Prozess erheblich.
  • Implementiere Fehlerbehandlungen im VBA-Code, um mögliche Laufzeitfehler abzufangen und das Makro stabiler zu machen.

FAQ: Häufige Fragen

1. Warum kann ich nur gefilterte Daten in das aktive Blatt kopieren? Weil Excel standardmäßig nur die sichtbaren Zellen berücksichtigt, wenn Du den Autofilter verwendest. Mit VBA kannst Du diese Funktionalität erweitern.

2. Funktioniert dieses Makro in allen Excel-Versionen? Das Makro sollte in Excel 2010 und neueren Versionen funktionieren, da die grundlegenden VBA-Funktionen seitdem nicht verändert wurden.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige