Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
VBA - PasteSpecial und selektierte Spalt
10.02.2021 11:40:47
Phillip
Hallo liebes Forum,
ich habe eine wahrscheinlich sehr einfache Frage.
Folgenden Code habe ich bereits zusammengesucht und dieser funktioniert auch soweit:

Private Sub CommandButton1_Click()
Dim ende As Long
Dim zelle As Range
If IsError(Evaluate("Tabelle1!A1")) Then
Worksheets.Add after:=Worksheets("Tabelle1")
End If
ende = Worksheets("Tabelle1").UsedRange.Rows.Count
For Each zelle In Worksheets("Tabelle1").Range("B3:B" & ende)
If Not IsEmpty(zelle) Then
zelle.EntireRow.Copy Worksheets("Tabelle2").Cells(Worksheets("Tabelle2").UsedRange. _
SpecialCells(xlLastCell).Row + 1, 1)
End If
Next zelle
End Sub

Ich habe nur noch zwei Wünsche:
Statt

zelle.EntireRow.Copy Worksheets("Tabelle2").Cells(Worksheets("Tabelle2").UsedRange.SpecialCells(xlLastCell).Row + 1, 1)

".EntireRow" sollen lediglich die Spalten A, B & C kopiert werden und dann auch nur die Werte und nicht die Formeln.
Mit ".PasteSpecial xlPasteValues" habe ich es schon versucht, aber ich weiß nicht, wo ich das in der Codezeile einbauen muss.
Vielleicht weiß ja jemand, wie diese Zeile modifiziert werden muss.
Ich bedanke mich und wünsche einen schönen Wintertag. :)
Liebe Grüße
Phillip

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
doppelt owt
10.02.2021 11:54:16
ralf_b
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 12:10:39
Piet
Hallo
ich habe mir erlaub den Code mal etwas umzuschreiben, konnte ihn aber Nicht testen! Bitte ausprobieren.
Durch die With Klammer wird die Schreibweise kürzer. Alle .Cells und .Range mit Punkte davor beziehen sich dann auf Tabelle2. Ausserdem liebe ich es die LastZell Zeile in eine Variable zu laden, das verkürzt auch die Schreibweise, macht den Code übersichlicher. Ich hoffe er funktioniert.
mfg Piet
Private Sub CommandButton1_Click()
Dim lz2 As Long    '** neu
Dim ende As Long
Dim zelle As Range
If IsError(Evaluate("Tabelle1!A1")) Then
Worksheets.Add after:=Worksheets("Tabelle1")
End If
ende = Worksheets("Tabelle1").UsedRange.Rows.Count
With Worksheets("Tabelle2")  '** s. vorgestellte Punkte
For Each zelle In Worksheets("Tabelle1").Range("B3:B" & ende)
If Not IsEmpty(zelle) Then
lz2 = .UsedRange.SpecialCells(xlLastCell).Row + 1
Worksheets("Tabelle1").Cells(zelle, 1).Resize(1, 3).Copy
.Cells(l2z, 1).PasteSpecial xlPasteValues
End If
Next zelle
End With
Application.CutCopyMode = False
End Sub

Anzeige
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 12:33:42
Phillip
Hallo Piet,
erst einmal toll, dass Du das so ad hoc umschreiben kannst.
So gannz durchsteigen tue ich noch nicht.
Ein Fehler wird mir noch ausgegeben für die Zeile 13.
Worksheets("Tabelle1").Cells(zelle, 1).Resize(1, 3).Copy

Die Datei habe ich mal hochgeladen:
https://www.herber.de/bbs/user/143791.xlsm
Vielen Dank für Deine schnelle Antwort.
Beste Grüße
Phillip
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 12:12:53
Piet
Nachtrag
diesen Teil bitte mal mit Select testen ob der Bereich stimmt.
Sub Test
zelle = 3
Worksheets("Tabelle1").Cells(zelle, 1).Resize(1, 3).Select
End Sub

Anzeige
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 12:46:36
Phillip
Hallo Piet,
ja, selektiert werden daraufhin Tabelle1 - A3:C3
Gruß
Phillip
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 13:44:00
Piet
Hallo Philipp
ohne die Datei gesehen zu haben sah ich aber sofort meinen Flğchtigkeitsfehler in der Codezeile!
Ersetze bitte Cells(zelle,1) durch Cells(zelle.Row, 1) - dann sollte es klappen.
mfg Piet
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 14:09:13
Phillip
Hallo Piet,
ich glaube wir haben es gleich.
Jetzt wirft er ein noch einen Fehler in folgender Zeile heraus:
 .Cells(l2z, 1).PasteSpecial xlPasteValues

Wahrscheinlich ist hier noch ein Bezug falsch?
Ich verstehe auch nicht, was das "12z" macht.
Hier noch einmal der gesamte Code:
Private Sub CommandButton1_Click()
Dim lz2 As Long    '** neu
Dim ende As Long
Dim zelle As Range
If IsError(Evaluate("Tabelle1!A1")) Then
Worksheets.Add after:=Worksheets("Tabelle1")
End If
ende = Worksheets("Tabelle1").UsedRange.Rows.Count
With Worksheets("Tabelle2")  '** s. vorgestellte Punkte
For Each zelle In Worksheets("Tabelle1").Range("B3:B" & ende)
If Not IsEmpty(zelle) Then
lz2 = .UsedRange.SpecialCells(xlLastCell).Row + 1
Worksheets("Tabelle1").Cells(zelle.Row, 1).Resize(1, 3).Copy
.Cells(l2z, 1).PasteSpecial xlPasteValues
End If
Next zelle
End With
Application.CutCopyMode = False
End Sub

Anzeige
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 14:12:49
Piet
Hallo
Ja, Sorry .Cells(l2z muss heissen: - .Cells(lz2
mfg Piet
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 14:34:09
Phillip
Piet, ganz stark! Das funktioniert.
Nur eine ästhetische Kleinigkeit:
Wenn ich den Button betätige auf Tabelle1, dann sieht kurz so aus, als würde er es auf Tabelle1 einfügen; tut er aber nicht, sondern packt es korrekterweise in Tabelle2.
Weißt Du, warum das passiert?
LG
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 17:37:15
Piet
Hallo Philipp
ja, der Effekt findet statt weil ich wohl vergessen habe am Code Anfang den Bildschirm abzuschalten. Application.ScreenUdatin = False must du gnnz oben nach Sub ... noch einfügen. Dann hört es auf.
mfg Piet
Anzeige
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 19:51:32
Phillip
Piet, Du bist super.
Mit
Application.ScreenUpdating = False
hat das funktioniert.
Vielen Dank für Deine schnelle Hilfe.
Dann ist der Beitrag hiermit auch erfolgreich beendet.
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 20:50:28
Phillip
Oh Piet, es gibt doch noch einen Fehler.
Er soll ja die Daten in Tabelle 2 packen.
Kann man das so machen, dass er das anhand der Spalte "A" mist.
Ich habe nämlich in Spalte F und Folgende ein SVERWEIS stehen, der, sofern Spalte "A" gefüllt ist, sich darauf bezieht und dann dazu Werte liefert.
Anscheinend kopiert er die Werte dahin, wo er in der gesamten Zeile keinen Wert oder Formel findet.
Eigentlich müsste er nur bei Spalte A schauen, den letzten Eintrag finden und dort anfügen. :)
Anzeige
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 20:59:31
ralf_b
.UsedRange ist der gesamte benutzte bereich. wie der name schon sagt.
und wenn mit .UsedRange.SpecialCells(xlLastCell).Row + 1 eine zeile darunter angesprochen wird ,ist auch klar das da sonst nichts steht in der Zeile.
versuchs mal mit .Columns(1).SpecialCells(xlLastCell).Row + 1
gruß
rb
update
10.02.2021 21:10:32
ralf_b
sorry habe das grad mal gecheckt. leider funktioniert das nicht.
das geht aber .Cells(Rows.Count, 1).End(xlUp).Row +1
AW: VBA - PasteSpecial und selektierte Spalt
10.02.2021 21:16:49
Phillip
Hallo Ralf,
diese Community ist echt klasse. Hätte nicht gedacht, dass die Leute so schnell und eiftig helfen.
Deine Anmerkung habe ich eingebaut, aber leider kopiert er die Werte dann immernoch erst dorthin, wo in Spalten F etc. keine Formel mehr vorhanden ist.
Hier mein Code (Gruppen_Check = Tabelle 1; Gruppenliste = Tabelle2):
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lz2 As Long    '** neu
Dim ende As Long
Dim zelle As Range
If IsError(Evaluate("Gruppen_Check!A1")) Then
Worksheets.Add after:=Worksheets("Gruppen_Check")
End If
ende = Worksheets("Gruppen_Check").UsedRange.Rows.Count
With Worksheets("Gruppenliste")  '** s. vorgestellte Punkte
For Each zelle In Worksheets("Gruppen_Check").Range("B3:B" & ende)
If Not IsEmpty(zelle) Then
lz2 = .Columns(1).SpecialCells(xlLastCell).Row + 1
Worksheets("Gruppen_Check").Cells(zelle.Row, 1).Resize(1, 3).Copy
.Cells(lz2, 1).PasteSpecial xlPasteValues
End If
Next zelle
End With
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Gruppen_Check").Range("B3:D50000").ClearContents
End Sub

Anzeige
siehe update post owT
10.02.2021 21:24:13
ralf_b
AW: siehe update post owT
10.02.2021 21:29:35
Phillip
YES, Ralf! Vielen lieben Dank. Passt perfekt!
Einen schönen Abend noch. :)
danke für die Rückmeldung owt
11.02.2021 14:23:50
ralf_b

309 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige