Soms willen we meerdere bladen samenvoegen tot één blad, zodat we de gegevens gemakkelijk kunnen analyseren en er nuttige informatie van kunnen maken. In dit artikel wordt uitgelegd hoe u met VBA meerdere werkbladen kunt samenvoegen tot één werkblad.
Voorbeeld:
Hier heb ik wat gegevens opgehaald van de server die gegevens naar verschillende werkbladen retourneert. Ik heb nog een blad toegevoegd en het als "Master" genoemd. Andere bladnamen doen er niet toe.
Voer nu deze macro uit.
Sub Merge_Sheets() Dim startRow, startCol, lastRow, lastCol As Long Dim headers As Range 'Set Master sheet voor consolidatie Set mtr = Worksheets("Master") Set wb = ThisWorkbook 'Get Headers Set headers = Application.InputBox("Selecteer de Headers", Type:=8) 'Kopieer kopteksten naar hoofdkoppen.Kopieer mtr.Range("A1") startRow = headers.Row + 1 startCol = headers.Column Debug.Print startRow, startCol 'loop door alle bladen voor elke ws In wb.Worksheets 'behalve het hoofdblad van looping If ws.Name "Master" Dan ws.Activate lastRow = Cells(Rows.Count, startCol).End(xlUp).Row lastCol = Cells(startRow, Columns.Count). End(xlToLeft).Column 'haal gegevens van elk werkblad en kopieer het naar het hoofdblad Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _ mtr.Range("A" & mtr.Cells(Rows) .Count, 1).End(xlUp).Rij + 1) End If Next ws Worksheets("Master").Activate End Sub
Hoe bladen samen te voegen met behulp van deze VBA-macro?
- Voeg een nieuw blad in en noem het "Master" in de werkmap. Hernoem het later als je wilt.
- Voeg een module in de VBA-editor in en kopieer de bovenstaande VBA-code.
- Voer de macro uit.
- U wordt gevraagd om koppen te selecteren. Selecteer de kop en druk op OK.
En het is gedaan. Alle bladen zijn samengevoegd in master.
Hoe het werkt?
Ik neem aan dat je de basis kent van het maken van objecten en variabelen in VBA. in het eerste deel hebben we object en variabelen gemaakt die we nodig zullen hebben in onze operaties.
Nou, de meeste dingen die ik heb uitgelegd met behulp van opmerkingen in vba-code. Laten we eens kijken naar het belangrijkste deel van deze vba-code.
Voor elke ws In wb.Worksheets 'behalve het hoofdblad van looping If ws.Name "Master" Dan ws.Activate lastRow = Cells(Rows.Count, startCol).End(xlUp).Row lastCol = Cells(startRow, Columns. Count).End(xlToLeft).Column 'haal gegevens op uit elk werkblad en kopieer het naar het hoofdblad Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _ mtr.Range("A" & mtr. Cellen(Rijen.Aantal, 1).End(xlUp).Rij + 1) End If Next ws
In eerdere artikelen hebben we geleerd hoe u door bladen kunt bladeren en hoe u de laatste rij en kolom kunt krijgen met vba.
Hier doorlopen we elk blad in de hoofdwerkmap met for-lus.
Voor elke ws In wb.Werkbladen
Vervolgens sluiten we het "master" -blad uit van lusvorming, omdat we onze gegevens in dat blad zullen consolideren.
Dan krijgen we het laatste rij- en laatste kolomnummer.
Nu is de volgende regel erg belangrijk. We hebben meerdere bewerkingen in één lijn gedaan.
Bereik (Cellen (startRij, startCol), Cellen (laatsteRij, laatsteCol)). Kopieer _
mtr.Bereik("A" & mtr.Cellen(Rijen.Aantal, 1).Einde(xlOmhoog).Rij + 1)
Eerst vormen we een bereik met startRow, startCol en lastRow en lastCol.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)) We hebben het gekopieerd met de kopieermethode van bereik. Bereik (Cellen (startRij, startCol), Cellen (lastRij, laatsteCol)). Kopiëren We hebben het rechtstreeks in de eerste lege cel geplakt na de laatste niet-lege cel in kolom A van het hoofdblad (mtr.Cells (Rows.Count, 1). End (xlOmhoog). Rij + 1). Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _ mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Rij + 1)
Deze lus wordt uitgevoerd voor alle bladen en kopieert de gegevens van elk blad naar het hoofdblad.
Ten slotte activeren we aan het einde van de macro het hoofdblad om de uitvoer te zien.
Dus ja jongens, dit is hoe je elk blad in een werkmap kunt samenvoegen. Laat het me weten als je vragen hebt over deze VBA-code of een ander Excel-onderwerp in de comments hieronder.
Download bestand:
Gerelateerde artikelen:
Hoe blader je door bladen
hoe de laatste rij en kolom te krijgen met vba
Bladen verwijderen zonder bevestigingsvragen met VBA in Microsoft Excel
Nieuwe werkmap toevoegen en opslaan met VBA in Microsoft Excel 2016
Geef een bericht weer op de Excel VBA-statusbalk
Schakel waarschuwingsberichten uit met VBA in Microsoft Excel 2016
Populaire artikels:
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?
