Manchmal möchten wir mehrere Blätter in einem Blatt zusammenführen, damit wir die Daten einfach analysieren und in nützliche Informationen umwandeln können. In diesem Artikel erfahren Sie, wie Sie mit VBA mehrere Arbeitsblätter zu einem Arbeitsblatt zusammenführen.
Beispiel:
Hier habe ich einige Daten vom Server geholt, die Daten in verschiedene Arbeitsblätter zurückgeben. Ich habe ein weiteres Blatt hinzugefügt und es als "Master" bezeichnet. Andere Blattnamen spielen keine Rolle.
Führen Sie nun dieses Makro aus.
Sub Merge_Sheets() Dim startRow, startCol, lastRow, lastCol As Long Dim headers As Range 'Stammblatt für Konsolidierung setzen Set mtr = Worksheets("Master") Set wb = ThisWorkbook 'Get Headers Set headers = Application.InputBox("Select the Headers", Type:=8) 'Copy Headers in master headers.Copy mtr.Range("A1") startRow = headers.Row + 1 startCol = headers.Column Debug.Print startRow, startCol 'Schleife durch alle Blätter für jedes ws In wb.Worksheets 'außer dem Master-Blatt von Schleifen Wenn ws.Name "Master" Then ws.Activate lastRow = Cells(Rows.Count, startCol).End(xlUp).Row lastCol = Cells(startRow, Columns.Count). End(xlToLeft).Column 'Daten von jedem Arbeitsblatt abrufen und in das Master-Blatt kopieren Range(Cells(startRow, startCol), Cells(lastRow, lastCol)). .Count, 1).End(xlUp).Row + 1) End If Next ws Worksheets("Master").Activate End Sub
Wie füge ich Blätter mit diesem VBA-Makro zusammen?
- Fügen Sie ein neues Blatt ein und nennen Sie es "Master" in der Arbeitsmappe. Benennen Sie es später um, wenn Sie möchten.
- Fügen Sie ein Modul in den VBA-Editor ein und kopieren Sie den obigen VBA-Code.
- Führen Sie das Makro aus.
- Sie werden aufgefordert, Überschriften auszuwählen. Wählen Sie die Überschrift aus und klicken Sie auf OK.
Und es ist vollbracht. Alle Blätter werden im Master zusammengeführt.
Wie es funktioniert?
Ich gehe davon aus, dass Sie die Grundlagen der Objekt- und Variablenerstellung in VBA kennen. Im ersten Teil haben wir Objekte und Variablen erstellt, die wir in unseren Operationen benötigen.
Nun, die meisten Dinge, die ich mit Kommentaren im VBA-Code erklärt habe. Schauen wir uns den Hauptteil dieses VBA-Codes an.
For Each ws In wb.Worksheets 'außer dem Master-Blatt von Schleifen Wenn ws.Name "Master" Then ws.Activate lastRow = Cells(Rows.Count, startCol).End(xlUp).Row lastCol = Cells(startRow, Columns. Count).End(xlToLeft).Column 'Daten von jedem Arbeitsblatt abrufen und in das Masterblatt kopieren Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _ mtr.Range("A" & mtr. Cells(Rows.Count, 1).End(xlUp).Row + 1) End If Next ws
In früheren Artikeln haben wir gelernt, wie man Blätter durchläuft und wie man mit vba die letzte Zeile und Spalte erhält.
Hier durchlaufen wir jedes Blatt in der Hauptarbeitsmappe mit der for-Schleife.
Für jedes ws in wb.Worksheets
Dann schließen wir das „Master“-Blatt von der Schleife aus, da wir unsere Daten in diesem Blatt konsolidieren werden.
Dann erhalten wir die letzte Zeile und die letzte Spaltennummer.
Jetzt ist die nächste Zeile sehr wichtig. Wir haben mehrere Operationen in einer Linie durchgeführt.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
Zuerst bilden wir einen Bereich mit startRow, startCol und lastRow und lastCol.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)) Wir haben es mit der Kopiermethode des Bereichs kopiert. Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Kopieren Wir haben es direkt in die erste leere Zelle nach der letzten nicht leeren Zelle in Spalte A des Mastersheets eingefügt (mtr.Cells(Rows.Count, 1).End (xlUp).Zeile + 1). Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _ mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
Diese Schleife wird für alle Blätter ausgeführt und kopiert die Daten jedes Blatts in das Masterblatt.
Schließlich aktivieren wir am Ende des Makros das Mastersheet, um die Ausgabe zu sehen.
Also ja, Leute, so können Sie jedes Blatt in einer Arbeitsmappe zusammenführen. Lassen Sie mich wissen, wenn Sie Fragen zu diesem VBA-Code oder einem Excel-Thema im Kommentarbereich unten haben.
Download-Datei:
In Verbindung stehende Artikel:
Blättern durchschleifen
So erhalten Sie die letzte Zeile und Spalte mit vba
Löschen Sie Blätter ohne Bestätigungsaufforderung mit VBA in Microsoft Excel
Hinzufügen und Speichern einer neuen Arbeitsmappe mit VBA in Microsoft Excel 2016
Anzeigen einer Nachricht in der Excel VBA-Statusleiste
Deaktivieren Sie Warnmeldungen mit VBA in Microsoft Excel 2016
Populäre Artikel:
Die SVERWEIS-Funktion in Excel
ZÄHLENWENN in Excel 2016
So verwenden Sie die SUMIF-Funktion in Excel