Excel VBA letzte Zeile ermitteln und kopieren

#exceltips #exceltricks #vbatutorial
In diesem Video zeige ich Dir,
wie Du die letzte befüllte Zeile und die letzte befüllte Spalte per VBA ermitteln kannst und wie Du die letzte befüllte Zeile in eine andere Tabelle kopieren kannst.
Viel Spaß beim Video! ❤
💥Excel VBA - Der leichte & schnelle Einstieg: amzn.to/3p2aZfJ
💥VBA mit Excel: Das umfassende Handbuch: amzn.to/3oMVPut

[@excelforyou007]

Wenn dir das Video gefällt, dann lass ein Abo da!

[@ds-tq8yw]

Wie immer, super Video Danke Moni 🙂

[@excelforyou007]

Dankeschön 🤗

[@ds-tq8yw]

@@excelforyou007 Hi Moni, leider haben sich zwei Tippfehler bei dir eingeschlichen . lz , lzz und wsb sind vom Type Variant. Du wolltest bestimmt das so schreiben : Dim lz As Long , ls As Integer und Dim wsB As Worksheet, wsZ As Worksheet . Den Type einer Variablen kann man so herausfinden : 1. Variable markieren 2. Rechte Mouse Taste drücken 3. QuickInfo auswählen Mir passieren auch ständig Tippfehler 😜 Meistens benutze ich so etwas : '####################################### Type tAuto Marke As String Modell As String Farbe As String Ps As Integer Kmh As Integer End Type Sub MeinAuto() Dim NewAuto As tAuto NewAuto.Farbe = "Rot" NewAuto.Kmh = 250 NewAuto.Marke = "VW" NewAuto.Modell = "Polo" NewAuto.Ps = 500 MsgBox "Marke : " & NewAuto.Marke MsgBox "Modell : " & NewAuto.Modell MsgBox "Farbe : " & NewAuto.Farbe MsgBox NewAuto.Ps & " PS" MsgBox NewAuto.Kmh & " Km/h" End Sub '########################################################################################## Ps. Wenn es dich nervt, das hier ständig so ein Blödsinn poste, dann sage mir einfach Bescheid 🙂 LG Dirk

[@frankm.7758]

Dankeschön, Ein Träumchen . Ich würde die Copy gerne von der einen Datei und Arbeitsmappe in eine andere kopieren und nicht im selben Tabellenblatt. Beide Dateien sind geöffnet. Wie geht das den?

[@ds-tq8yw]

'Nicht getestet, da ich bei meinen Schwiegereltern bin 😞 ' Habe ich mit dem Handy eingetippt. ' Wenn diese Prozedur funktioniert , antworte mal. '################################################################################# Sub Datenkopieren() 'Definieren der Quell-Arbeitsmappe, des Tabellenblatts und des Bereichs, der kopiert werden soll Dim QuellArbeitsmappe As Workbook On Error Resume Next Set QuellArbeitsmappe = Workbooks("Name der Quell-Arbeitsmappe.xlsm") On Error GoTo 0 If QuellArbeitsmappe Is Nothing Then MsgBox "Die Quell-Arbeitsmappe ist nicht geöffnet." Exit Sub End If Dim QuellTabellenblatt As Worksheet Set QuellTabellenblatt = QuellArbeitsmappe.Worksheets("Name des Quell-Tabellenblatts") Dim QuellBereich As Range Set QuellBereich = QuellTabellenblatt.Range("A1:B10") 'Ersetze A1:B10 durch den Bereich, den Du kopieren möchtest 'Definieren der Ziel-Arbeitsmappe, des Tabellenblatts und der Zelle, in die der Bereich eingefügt werden soll Dim ZielArbeitsmappe As Workbook On Error Resume Next Set ZielArbeitsmappe = Workbooks("Name der Ziel-Arbeitsmappe.xlsx") On Error GoTo 0 If ZielArbeitsmappe Is Nothing Then Set ZielArbeitsmappe = Workbooks.Open("Pfad zur Ziel-Arbeitsmappe.xlsx") If ZielArbeitsmappe Is Nothing Then MsgBox "Die Ziel-Arbeitsmappe konnte nicht geöffnet werden." Exit Sub End If End If Dim ZielTabellenblatt As Worksheet Set ZielTabellenblatt = ZielArbeitsmappe.Worksheets("Name des Ziel-Tabellenblatts") Dim ZielZelle As Range Set ZielZelle = ZielTabellenblatt.Range("A1") 'Ersetze A1 durch die Zelle, in der Du mit dem Einfügen beginnen möchtest 'Kopieren und Einfügen des Bereichs QuellBereich.Copy Destination:=ZielZelle End Sub 'Ich hoffe, ich konnte Dir weiter helfen :-) 'Gruß 'Dirk

[@ds-tq8yw]

'Ich benutze für dynamische Daten das ListObject. 'Kopiere diese Prozedur in eine !!! Neue !!! .xlsm Arbeitsmappe . 'Getestet und läuft 🙂 Sub ErstelleUndFuelleTabelle() ' Variablen definieren Dim tbl As ListObject Dim ws As Worksheet Dim neueZeile As ListRow Dim NameInput As String Dim AlterInput As Variant Dim StadtInput As String ' Bezug auf Arbeitsblatt setzen Set ws = ThisWorkbook.Sheets("Tabelle1") ' Tabelle überprüfen, ob vorhanden, andernfalls neue Tabelle mit 3 Spalten erstellen und "Name", "Alter" und "Stadt" als Spaltenüberschriften setzen On Error Resume Next Set tbl = ws.ListObjects("tblFrankSeineDaten") If tbl Is Nothing Then Set tbl = ws.ListObjects.Add(xlSrcRange, Range("A1:C1"), , xlYes) tbl.Name = "tblFrankSeineDaten" tbl.HeaderRowRange(1) = "Name" tbl.HeaderRowRange(2) = "Alter" tbl.HeaderRowRange(3) = "Stadt" tbl.ListRows.Add tbl.ListRows.Add tbl.ListRows.Add tbl.DataBodyRange(1, 1) = "Frank" tbl.DataBodyRange(1, 2) = 35 tbl.DataBodyRange(1, 3) = "Berlin" tbl.DataBodyRange(2, 1) = "Dirk" tbl.DataBodyRange(2, 2) = 43 tbl.DataBodyRange(2, 3) = "Lüneburg" tbl.DataBodyRange(3, 1) = "Moni" tbl.DataBodyRange(3, 2) = 47 tbl.DataBodyRange(3, 3) = "Salzgitter" On Error GoTo 0 End If Eingabe: ' Benutzereingabe für neue Zeile abfragen NameInput = InputBox("Gebe einen Namen ein:") KeineZahl: AlterInput = InputBox("Gebe das Alter ein:") ' Fehlerbehandlung für AlterInput - sicherstellen, dass es sich um eine Zahl handelt If Not IsNumeric(AlterInput) Then MsgBox "Das Alter muss eine Zahl sein." GoTo KeineZahl End If StadtInput = InputBox("Gebe die Stadt ein:") ' Neue Zeile zur Tabelle hinzufügen Set neueZeile = tbl.ListRows.Add neueZeile.Range(1, 1).Value = NameInput neueZeile.Range(1, 2).Value = AlterInput neueZeile.Range(1, 3).Value = StadtInput End Sub 'Viel Spaß beim testen 🙂 ' Gruß 'Dirk

[@markusnoller275]

Hallo Moni, ich bin Markus, komme aus Oberschwaben (n der Nähe von RV) und freue mich eine vertrauten Dialekt (Kemptener Gegend, oder?) zu hören. Ich bin durch Zufall über deinen Kanal gestolpert. Sehr interessant. Programmiere auch VBA und freue mich immer, wenn ich was entdecken kann. Zu deinem Script deine Deklaration ab 10:14 Dim lz, ls as integer ist riskant... Wenn ich dich richtig verstanden habe, wolltest Du mit deinem Code lz und LS beides als integer deklarieren... Excel macht das aber nicht so: Dim lz, ... bewirkt, dass lz als Variant deklariert wird. der Teil dahinter "...ls as integer versteht Excel wieder richtig und deklariert ls als Integer-Variable Du kannst z.B. folgendes machen, um Dich zu überzeugen, das lz tatsächlich als Variant deklariert wurde Option Explicit Sub TestVariant Dim lz, ls as integer lz ="Moni" msgbox lz End Sub Du bekommst keine Fehlermeldung, sondern die Msg-Box wirft brav "Moni" aus. 🙂 Im Überwachungsfenster müsstest Du beim Datentyp Variant/String sehen, wenn Du lz überwachen lässt (weil Du ja lz den Wert "Moni" zugewiesen hast.) Bei ls müsste integer stehen, weil VBA den Datentyp ja richtig als integer deklariert hat Dein Code funktioniert trotzdem, weil Variant alle Datentypen aufnehmen kann. Falls Microsoft jedoch irgendwann mal die Einstellungen ändert, dass Variablen, denen nicht mit as... explizit eine Datentyp mitgegeben wird, nicht mehr standardmäßig als Variant, sondern z.B. als String deklariert wird, dürfte dein Code wahrscheinlich nicht mehr wie gewünscht funktionieren. Deshalb ist es aus meiner Sicht besser und sicherer, den Datentyp immer mitzugeben. Deine Deklarationszeile würde dann so aussehen: Dim lz as integer, ls as integer Ansonsten ein super Video. LG vom (fast) Bodensee ge Kempda und ein schönes Wochenende Markus

[@excelforyou007]

Hallo Markus, vielen Dank für deinen Hinweis. Mit Kempten lagst du gar nicht so schlecht...🙂 Ich wünsche dir auch ein schönes Wochenende. Viele Grüße Moni

[@markusnoller275]

@@excelforyou007 Danke Dir. Wie zuverlässig klappt diese Methode bei Dir? Ich durfte schon öfters feststellen, dass Excel, offensichtlich Schwierigkeiten hat die letze befüllte Zeile herauszufinden... Ich behelfe mir im Moment so, dass ich zum einen über "find" und "usedrange" einen Wert ermittle und danach nochmal spaltenweise mit "deiner" Methode den jeweils größten Wert für die letzte Zeile ermittle und schließlich den insgesamt größten Wert nehme. Hat Excel evtl. ein Problem damit, dass die Tabelle rund 120 Spalten und fast 18.000 Zeilen hat? (Tendenz steigend, da fast täglich neue Datensätze dazu kommen) Hast Du sonst noch eine Idee, wie ich zuverlässig die letze Zeile und Spalte einer Tabelle ermitteln kann, in der ein Wert (egal ob String oder numerisch) drin steht. Leider ist der Umstieg auf Access keine Option. Dank schee schomohl. LG nomohl ge Kempda. Markus

[@excelforyou007]

@@markusnoller275 bisher hatte ich mit dieser Methode keine Probleme. Aktuell fällt mir keine Alternative ein. Vielleicht hat die Community eine Idee. Viele Grüße Moni

[@markusnoller275]

@@excelforyou007 Hallo Moni, guten Morgen, Danke Dir LG Markus

[@jessilui6635]

Vielleicht kannst Du mir helfen. Ich benötige nicht die letzte Zeile aus der ich kopieren will, sondern ich will aus dem Eingabefeld K5:M:5 in die letzte Zeile kopieren. Also nicht den letzten Zelle ermitteln und von dort kopieren, sondern vom festen Bereich in die letzte Zeile. Wenn möglich noch im gleichen Datenblatt. Das wäre cool.

[@excelforyou007]

Anbei eine Möglichkeit: et wsB = Sheets("Beispiel") '

[@Meenzer83]

Wie ändert man das ganze ab das die letzten 5 Zeilen immer kopiert werden???

[@NachbarFizgo]

Die Variablendeklaration ist falsch bzw. unvollständig: "lz" ist kein Integer und "WsB" kein Worksheet, sondern beide sind vom Typ "Variant", weil man in VBA eben NICHT mehrere Variablen durch Komma getrennt vom gleichen Typ deklarieren kann. Der Typ muss für jede einzelne Variable separat angegeben werden (was allerdings auf einer Zeile erfolgen kann, z.B. "Dim lz as Integer, ls as Integer"). Funktionieren tut das Ganze hier dennoch, weil durch zu Zuweisung von Werten ("Set WsB = Sheets ...") aus dem Variant dann der entsprechende Typ gebilded wird!

[@excelforyou007]

Vielen lieben Dank für die Information.

[@mathiasmollmann9754]

Nichts Entwicklertools und VBA. Es muss doch eine ganz einfache Formel geben, um den Inhalt der letzten Zeile anzuzeigen.