Konsolidieren / Zusammenführen von mehreren Arbeitsblatt in ein Vorlagenblatt VBA
Manchmal möchten wir mehrere Blätter zu 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 abgerufen, die Daten in verschiedene Arbeitsblätter zurückgeben. Ich habe ein weiteres Blatt hinzugefügt und es als „Meister“ 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
'Set Master sheet for consolidation
Set mtr = Worksheets("Master")
Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)
'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column
Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Master" Then
ws.Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.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 in der Arbeitsmappe „Master“. 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 und klicken Sie auf OK.
Und es ist geschafft. Alle Blätter werden im Master zusammengeführt.
Wie funktioniert es?
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 für unsere Operationen benötigen.
Nun, die meisten Dinge habe ich mit Kommentaren im VBA-Code erklärt.
Schauen wir uns den Hauptteil dieses VBA-Codes an.
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Master" Then
ws.Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
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 abruft.
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 Zeile durchgeführt.
Bereich (Zellen (startRow, startCol), Zellen (lastRow, lastCol)). Kopieren Sie _ 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))
We have copied it using copy method of range.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy
We pasted it directly into first blank cell after last non blank cell in column A of master sheet (mtr.Cells(Rows.Count, 1).End(xlUp).Row + 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 Blattes in das Hauptblatt.
Schließlich aktivieren wir am Ende des Makros das Masterblatt, 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.
Datei herunterladen:
`link: /wp-content-uploads-2019-11-Consolidate_Merge-multiple-worksheets-into-one-master-sheet-using-VBA.xls [__ Consolidate_Merge mehrere Arbeitsblätter mit VBA zu einem Master Sheet zusammen]
Verwandte Artikel:
So durchlaufen Sie Blätter
wie man die letzte Zeile und Spalte mit vba
erhält //files-workbook-and-worksheets-in-vba/delete-sheets-without-confirmation-prompts-using-vba-in-microsoft-excel.html[Löschen Sie Blätter ohne Bestätigungsaufforderungen mit VBA in Microsoft Excel] `
link: / files-workbook-and-worksheets-in-vba-add-and-save-new-workbook-using-vba-in-microsoft-excel [Neue Arbeitsmappe mit VBA in Microsoft Excel 2016 hinzufügen und speichern]
link: / menüs-Symbolleisten-Statusleiste-in-vba-Anzeige-einer-Nachricht-in-der-Statusleiste-mit-vba-in-Microsoft-Excel [Anzeige einer Nachricht in der Excel VBA-Statusleiste]
link: / allgemeine-themen-in-vba-ausschalten-warnwarnmeldungen-mit-vba-in-microsoft-excel [Warnmeldungen mit VBA in Microsoft Excel 2016 ausschalten]
Beliebte Artikel:
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Die VLOOKUP-Funktion in Excel]
link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016]
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]