PasteSpecial Problem

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: PasteSpecial Problem
von: Christian
Geschrieben am: 03.11.2015 15:57:13

Hallo zusammen,
nach langer Zeit des hilfreichen "mitlesens" und adaptieren von genannten Lösungsvorschlägen bin ich nun nicht drumherum gekommen mich hier anzumelden und meine eigene Frage zu stellen.
Ich habe eine Datei mit mehreren Tabellenblättern. Ein Blatt dient als Gesamtübersicht während die anderen mit Daten befüllt werden. Anschliessend werden diese per Makro in die Gesamtübersicht übernommen und in der Quelltabelle gelöscht.
Hierbei ist es wichtig, dass nur Eingaben übernommen werden, die in Spalte "L" ein "Ja" haben. Anschliessend sollen per Knopfdruck alle Zeilen, die in Spalte "L" ein "Ja" haben in die Gesamtübersicht kopiert werden.
Meine Krux ist nun, dass der Code Funktioniert. Ich möchte aber nur die Werte kopieren, nicht die Formate etc. Also versuchte ich es mit ".PasteSpecial". Aber egal wie und wo ich es einbaue... es kommt immer zum Fehler.
Meine Bitte an die Community wäre, dass mir jemand zeigen könnte, wie ich im nachfolgenden Code ".PasteSpecial" einbauen kann.
Außerdem möchte ich beim Löschen der Daten im Quelldatenblatt (User 1) Leerzeilen unterbinden - die Zeilen dürfen aber nicht hart gelöscht werden (Es ist ein Arbeitsbereich von 100 Zeilen definiert und beim löschen würde dieser um die gelöschten Zeilen reduziert - Formatierung ginge ebenfalls verloren).
Anbei mein Code:
-----------------------------------------------------------


Private Sub CommandButton2_Click()
Dim i As Long
With Sheets("User 1")
    For i = .Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
        If .Cells(i, "L") Like "Ja" Then
            .Rows(i).Copy Sheets("Gesamtübersicht").Cells(Sheets("Gesamtübersicht").Cells(Rows. _
Count, "J").End(xlUp).Row + 1, "A")
            .Rows(i).Clear
        End If
    Next
End With
Unload Me
End Sub
-----------------------------------------------------------
Ich danke euch vorab für eure Hilfe und freue mich auf eine Antwort.
Gruß
Christian

Bild

Betrifft: AW: PasteSpecial Problem
von: daniel
Geschrieben am: 03.11.2015 16:06:45
Hi
zum PasteSpecial ersetze die Zeile
Rows(i).Copy Sheets("Gesamtübersicht").Cells(Sheets("Gesamtübersicht").Cells(Rows.Count, "J").End(xlUp).Row + 1, "A")
durch folgenden zwei Zeilen:
Rows(i).Copy
Sheets("Gesamtübersicht").Cells(Sheets("Gesamtübersicht").Cells(Rows.Count, "J").End(xlUp).Row + 1, "A").PasteSpecial xlpastevalues

Gruss Daniel

Bild

Betrifft: AW: PasteSpecial Problem
von: Christian
Geschrieben am: 03.11.2015 16:15:41
Hallo Daniel,
Vielen Dank für deine schnelle Hilfe!
Es funktioniert einwandfrei. Ich hatte bereits das PasteSpecial an das Ende gesetzt. Aber das es so auf zwei Zeilen aufgeteilt werden muss - da wäre ich nie drauf gekommen.
- Jetzt wäre da noch meine zweite Bitte: wie bekomme ich die kopierten Zellen im Tabellenblatt "User 1" gelöscht ohne dass der zuvor definierte Arbeitsbereich mit Leerzeilen zurückbleibt oder das Format zerschossen wird?
Der Befehl .Clear löscht mir einfach nur den Inhalt der kopierten Zeilen. Wenn diese aber von Zeilen umgeben ist, die nicht kopiert wurden, habe ich eine Leerzeile - die ich vermeiden möchte.
Vielen Dank!

Bild

Betrifft: AW: PasteSpecial Problem
von: matthias
Geschrieben am: 03.11.2015 16:18:58
Hallo Christian,
das nennt sich dann .Delete
lg Matthias

Bild

Betrifft: AW: PasteSpecial Problem
von: Christian
Geschrieben am: 03.11.2015 16:27:52
Hallo Matthias,
Danke hierfür.
Aber wenn ich .Delete einsetze wird mir die gesammte Zeile hart aus dem Excel-Sheet gelöscht.
Gibt es eine Möglichkeit, dass ich nach jedem kopieren die restlichen Daten (immer ab Zeile 11 von Spalte A bis L) neu sortiere um die Leerzeilen zu vermeiden?
.Delete kann ich nicht verwenden, da der zuvor definierte Arbeitsbereich jedesmal um die gelöschten Zeilen kleiner wird. Der bereicht enthält eingebette Dropdown-Menüs und andere definierte Gültigkeiten, die nach dem Löschen weg wären.
Vielen Dank und viele Grüße
Christian

Bild

Betrifft: AW: PasteSpecial Problem
von: matthias
Geschrieben am: 03.11.2015 16:32:08
Dann eben so:

.Range("A5:L100").Sort .Columns("A"), Order1:=xlAscending, Header:=xlNo

Die Sortierspalte ist hier als A angegeben und muss, ebenso wie der Bereich welcher sortiert werden soll, von dir geändert werden.
lg Matthias

Bild

Betrifft: AW: PasteSpecial Problem
von: Christian
Geschrieben am: 03.11.2015 16:56:20
Danke Matthias,
das funktionert so weit gut.
Aber jetzt zeigt sich hier ein neues Problem.
Mein Code bis jetzt:

Private Sub CommandButton2_Click()
Dim i As Long
With Sheets("User 1")
    For i = .Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
        If .Cells(i, "L") Like "Ja" Then
            Rows(i).Copy
            Sheets("Gesamtübersicht").Cells(Sheets("Gesamtübersicht").Cells(Rows.Count, "J"). _
End(xlUp).Row + 1, "A").PasteSpecial xlPasteValues
            .Rows(i).Clear
            .Range("A11:L110").Sort .Columns("A"), Order1:=xlAscending, Header:=xlNo
        End If
    Next
End With
Unload Me
End Sub

Er sortiert mir zwar neu und verkleinert auch nicht den definierten Arbeitsbereicht. Aber nun werden beim Sortieren die Gültigkeiten und vordefinierten bedingten Formatierungen gelöscht (nur bei den Leerzeilen, die durch das kopieren und .Clear entstehen). Wenn ich von Hand sortiere, bleiben die Formatierungen und Gültigkeiten für jede Zelle bestehen. Nur nicht beim obigen Makro ?

Bild

Betrifft: AW: PasteSpecial Problem
von: matthias
Geschrieben am: 03.11.2015 17:00:35
Aso, dann verwende statt .Clear nur .ClearContents
Als Content wird nur der Wert bzw. Formel in der Zelle angesehen. Clear löscht grundsätzlich alles außer die Zelle selbst und vereint somit ClearContents und ClearFormats.
lg Matthias

Bild

Betrifft: AW: PasteSpecial Problem
von: Christian
Geschrieben am: 03.11.2015 17:18:22
.ClearContents war das was hier gefehlt hat.
Aber jetzt habe ich mir wohl irgendwas zerschossen.
Mein Code aktuell:

Private Sub CommandButton2_Click()
Dim i As Long
With Sheets("User 1")
    For i = .Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
        If .Cells(i, "L") Like "Ja" Then
            Rows(i).Copy
            Sheets("Gesamtübersicht").Cells(Sheets("Gesamtübersicht").Cells(Rows.Count, "J"). _
End(xlUp).Row + 1, "A").PasteSpecial xlPasteValues
            .Rows(i).ClearContents
            .Range("A11:L110").Sort .Columns("A"), Order1:=xlAscending, Header:=xlNo
        End If
    Next
End With
Unload Me
End Sub
Jetzt liegt das Problem nicht mehr auf dem Tabellenblatt, das kopiert und sortiert wird. Sondern auf dem Tabellenblatt "Gesamtübersicht" hier kommt jetzt so gut wie gar nichts mehr an. Mal kommt nur eine Zeile an oder auch gar nichts. Wenn ich erneut kopiere, dann werden mir die bestehenden Daten einfach überschrieben, was ich aber zuvor doch eigentlich unterbunden hatte da ich ja extra in die letzte, freie Zeile schreiben will.
Kann auch sein, dass ich mich unverständlich ausdrücke?
Vielen Dank das Du Dich so beharlich meiner Probleme annimmst!

Bild

Betrifft: AW: PasteSpecial Problem
von: daniel
Geschrieben am: 03.11.2015 17:32:42
Hi
sortieren solltest du erst am Schluss, nach der Schleife.
ansonsten, warum arbeitets du nicht mit dem Autofilter?
das spart dir die Schleife, weil du alle Zellen auf einen Schlag kopieren und leeren kannst.
in gefilterten Tabellen werden nur die sichtbaren Zeilen kopiert und beim Eifügen, dann lückenlos eingefügt.
auch beim Löschen werden nur die sichtbaren Zeilen bearbeitet.
im Prinzip wäre der Code dann folgender (das vorgestellte Sortieren nach dem Kriterium beschleunigt den Kopiervorgang)

with Sheets("User 1").UsedRange
.sort Key1:=.Cells(1, 12), order1:=xlascending, Header:=xlyes
.Autofilter Field:=12, Criteria1:="Ja"
.Offset(1, 0).Copy
Sheets("Gesamtübersicht").Cells(Rows.Count, 10).end(xlup).Offset(1, -9).PasteSpecial  _
xlpastevalues
.Offset(1, 0).Clearcontents
.Sort Key1:=.Cells(1, 1), order1:=xlascending, Header:=xlyes
end with
Gruß Daniel

Bild

Betrifft: AW: PasteSpecial Problem
von: Christian
Geschrieben am: 03.11.2015 17:59:41
Hallo Daniel,
Ich habe das Sortieren nach der Schleife gesetzt. Aber es wird nur eine einzige Zeile kopiert bzw. immer vorhandene Zeilen im Tabellenblatt "Gesamtübersicht" überschrieben. Was mache ich hier bitte falsch?
Vorher ging das kopieren ohne Fehler, jetzt mit den vielen Änderungen die ich hier genannt bekommen habe geht das nicht mehr :-/

Private Sub CommandButton2_Click()
Dim i As Long
With Sheets("User 1")
    For i = .Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
        If .Cells(i, "L") Like "Ja" Then
            Rows(i).Copy
            Sheets("Gesamtübersicht").Cells(Sheets("Gesamtübersicht").Cells(Rows.Count, "J"). _
End(xlUp).Row + 1, "A").PasteSpecial xlPasteValues
            .Rows(i).ClearContents
                    End If
                     Next
                    .Range("A11:L110").Sort .Columns("A"), order1:=xlAscending, Header:=xlNo
End With
Unload Me
End Sub
Das Sortieren klappt so gut. Aber jetzt ist mein Problem, dass im Ziel-Tabellenblatt nur noch eine Zeile ankommt. bzw. die erste Zeile immer wieder überschrieben wird anstatt fortlaufen.
Vielen lieben Dank und Gruß
Christian

Bild

Betrifft: AW: PasteSpecial Problem
von: daniel
Geschrieben am: 03.11.2015 18:17:26
Hi
vielleicht beim Rows(1).Copy den Punkt vergessen?
wenn nicht zufälligerweise das Blatt "User 1" aktiv ist, dann wird da nix kopiert.
also solltest du den Punkt davor setzen, damit sich das auch auf das bei WTIH definierte Blatt bezieht.
damit End(xlup) richtig trifft, darf Spalte J nicht leer sein.
wenn in irgendeiner kopierten Zeile die Spalte J leer ist, wird diese immer wieder überschrieben.
Gruß Daniel

Bild

Betrifft: AW: PasteSpecial Problem
von: Christian
Geschrieben am: 03.11.2015 18:48:58
Danke Daniel!
Der Punkt hat gefehlt.
Ich habe den Code sicherheitshalber nochmal eingegeben und siehe da... aktuell funktioniert alles wie gewünscht.
Ich danke euch beiden (Daniel und Matthias) für eure Hilfe. Falls sich wieder ein Problem auftut - schreibe ich es.
Gruß
Christian

 Bild

Beiträge aus den Excel-Beispielen zum Thema "PasteSpecial Problem"