Note: The other languages of the website are Google-translated. Back to English

Hoe gegevens opsplitsen in meerdere werkbladen op basis van kolom in Excel?

Stel dat u een werkblad heeft met enorme rijen gegevens, en nu moet u de gegevens opsplitsen in meerdere werkbladen op basis van de Naam kolom (zie volgende schermafbeelding), en de namen worden willekeurig ingevoerd. Misschien kunt u ze eerst sorteren en ze vervolgens een voor een kopiëren en in andere nieuwe werkbladen plakken. Maar dit vereist uw geduld om herhaaldelijk te kopiëren en plakken. Vandaag zal ik het hebben over enkele snelle trucs om deze taak op te lossen.

doc gegevens opgesplitst in kolommen 1

Splits gegevens in meerdere werkbladen op basis van kolom met VBA-code

Splits gegevens in meerdere werkbladen op basis van kolom met Kutools voor Excel


Splits gegevens in meerdere werkbladen op basis van kolom met VBA-code

Als u de gegevens snel en automatisch op basis van kolomwaarde wilt splitsen, is de volgende VBA-code een goede keuze. Doe alstublieft als volgt:

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het modulevenster.

Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub

3. Druk vervolgens op F5 toets om de code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren de koptekstrij te selecteren, zie screenshot:

doc gegevens opgesplitst in kolommen 7

4. En klik dan OK knop, en selecteer in het tweede promptvenster de kolomgegevens die u wilt splitsen op basis van, zie screenshot:

doc gegevens opgesplitst in kolommen 8

5. Dan klikken OK, en alle gegevens in het actieve werkblad worden opgesplitst in meerdere werkbladen op basis van de kolomwaarde. En de gesplitste werkbladen krijgen de naam van de gesplitste celnamen. Zie screenshot:

doc gegevens opgesplitst in kolommen 2

Opmerking:: De gesplitste werkbladen worden aan het einde van de werkmap geplaatst waar het hoofdwerkblad zich bevindt.


Splits gegevens in meerdere werkbladen op basis van kolom met Kutools voor Excel

Als Excel-beginner is deze lange VBA-code enigszins moeilijk voor ons, en de meesten van ons weten zelfs niet hoe ze de code naar behoefte moeten aanpassen. Hier zal ik je een multifunctionele tool voorstellen--Kutools for Excel, het is Gegevens splitsen hulpprogramma kan u niet alleen helpen om gegevens te splitsen in meerdere werkbladen op basis van kolom, maar kan ook gegevens splitsen op basis van het aantal rijen.

Opmerking:Om dit toe te passen Gegevens splitsen, ten eerste moet u het Kutools for Excelen pas de functie vervolgens snel en gemakkelijk toe.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer het gegevensbereik dat u wilt splitsen.

2. Klikken Kutools Plus > Werkblad > Gegevens splitsen, zie screenshot:

doc gegevens opgesplitst in kolommen 3

3. In de Gegevens splitsen in meerdere werkbladen dialoogvenster, moet u:

1). Selecteer Specifieke kolom optie in het Splitsen op basis van sectie en kies de kolomwaarde waarop u de gegevens wilt splitsen op basis van de vervolgkeuzelijst. (Als uw gegevens kopteksten hebben en u deze in elk nieuw gesplitst werkblad wilt invoegen, controleer dan Mijn gegevens hebben kopteksten keuze.)

2). Vervolgens kunt u de namen van het gesplitste werkblad specificeren onder de Nieuwe werkbladnaam sectie, specificeert u de regels voor werkbladnamen uit het Reglement vervolgkeuzelijst, kunt u de Voorvoegsel or Achtervoegsel ook voor de bladnamen.

3). Klik op de OK knop. Zie screenshot:

doc gegevens opgesplitst in kolommen 4

4. Nu worden de gegevens opgesplitst in meerdere werkbladen in een nieuwe werkmap.

doc gegevens opgesplitst in kolommen 5

Klik om Kutools voor Excel en nu gratis uit te proberen!


Splits gegevens in meerdere werkbladen op basis van kolom met Kutools voor Excel

Kutools for Excel bevat meer dan 300 handige Excel-tools. Gratis te proberen zonder beperking binnen 30 dagen. Download nu de gratis proefversie!


Gerelateerd artikel:

Hoe gegevens opsplitsen in meerdere werkbladen op basis van het aantal rijen?


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken!
officetab onderkant
Comments (291)
Nog geen beoordelingen. Beoordeel als eerste!
Deze opmerking is gemaakt door de moderator op de site
Gegevens splitsen in meerdere werkbladen op basis van kolom met VBA-code vertoont een fout. probeer het te corrigeren en hetzelfde bij te werken. Als u de voorbeeld Excel-bestanden aanlevert, zal dit erg nuttig zijn.
Jagadesh.K
Deze opmerking is gemaakt door de moderator op de site
Hallo, bedankt voor de code, het werkt voor mij! Ik probeer een code te vinden die een hoofdblad in meerdere bladen splitst op basis van datum
Georges
Deze opmerking is gemaakt door de moderator op de site
Dat was ongelooflijk! Dit proces zou me meer dan een uur hebben gekost, maar het was binnen 30 seconden gedaan. Deze bewaar ik voor mijn VBA-bibliotheek. Dank u!
Heidi
Deze opmerking is gemaakt door de moderator op de site
Hallo, ik heb 30000 cellen in mijn werkblad en moet ze in maanden opsplitsen. is er een code die ik kan gebruiken om het sneller te doen. Ik heb 8 kolommen en de datum is kolom B. Ik heb gespeeld met de bovenstaande code die wordt gegeven, maar het faalt enorm. Kunt u mij alstublieft hiermee helpen. Bij voorbaat dank
Sarah
Deze opmerking is gemaakt door de moderator op de site
Ik krijg de volgende foutmelding: Runtime error '6' overflow Na het debuggen toon de regel For i = 2 To Ir Mijn Excel-rijen zijn meer dan 500,000. Is er een oplossing Heel erg bedankt voor de code. Met vriendelijke groet Lok
Lok
Deze opmerking is gemaakt door de moderator op de site
Hallo, Heel erg bedankt voor de code. ik krijg de volgende foutmelding: Runtime error '6' overflow op de regel For i = 2 To Ir Een oplossing hiervoor. Dank u
Lok
Deze opmerking is gemaakt door de moderator op de site
Ik krijg een foutmelding als ik op F5 druk - GoTo Box vraagt ​​om een ​​referentie??
Cindy
Deze opmerking is gemaakt door de moderator op de site
Het VBA-proces werkte perfect, heel erg bedankt voor het delen van je expertise en het bespaart me veel tijd!
Niki
Deze opmerking is gemaakt door de moderator op de site
De VBA-code werkte perfect. Het lijkt erop dat de bladen niet worden bijgewerkt als er wijzigingen worden aangebracht in Blad1. Gelieve te helpen.
Vickey
Deze opmerking is gemaakt door de moderator op de site
Hallo, bedankt voor de code, het werkt voor mij! Ik heb slechts twee vragen/opmerkingen. 1 de gekopieerde gegevens bevatten niet de lay-out van het originele bestand. Zou het mogelijk zijn om de gegevens als tabel met autofilter te kopiëren? 2 lijken de gekopieerde gegevens niet aan het titelbereik te zijn gebonden. Is het mogelijk om de code aan te passen voor een specifiek bereik of tabelnaam? Deze aanpassing zou zeer nuttig zijn. Groetjes, Pieter
Pieter
Deze opmerking is gemaakt door de moderator op de site
Werkt perfect! Dank u.
Belinda Martinez
Deze opmerking is gemaakt door de moderator op de site
Werkt als een tierelier... Bedankt voor de premium code... :lol:
Bobby M
Deze opmerking is gemaakt door de moderator op de site
Heel erg bedankt, dit werkte prima. Wat gebeurt er echter als ik de gegevens op elk tabblad opnieuw wil sorteren (met behulp van een andere kolom)? Kortom, deze VBA verdeelt het in tabbladen, maar ik wil misschien dat het verder wordt uitgesplitst ... is dat mogelijk?
Jonathan
Deze opmerking is gemaakt door de moderator op de site
Hallo Jonathan, Oude opmerking weet ik, maar kan in de toekomst nuttig zijn voor anderen: ik moest dit doen, maar kon geen gemakkelijke manier vinden met de VBA. Ik ontdekte echter dat als je een nieuwe kolom in je spreadsheet maakt als een samensmelting van de 2, dus bijv. =A1&" "&A2 Dit geeft je dan 1 cel met beide sets informatie. U kunt dan de bovenstaande module uitvoeren en het werkt prima! Bewerken - De gegevens in de kolommen moeten korter zijn dan 30 tekens, anders worden de gegevens niet gekopieerd (wordt weergegeven als een fout in de module) en krijgt u een leeg blad in het midden van uw nieuwe bladen.
Ben
Deze opmerking is gemaakt door de moderator op de site
Geweldig.. Dit was adembenemend. Ik worstelde al zo lang met dit probleem en deze code kwam als een verademing. Bedankt voor het delen ervan.
Aditi
Deze opmerking is gemaakt door de moderator op de site
Verbazingwekkend. Bedankt voor het plaatsen.
Gopinath
Deze opmerking is gemaakt door de moderator op de site
De code werkte als een tierelier voor kleinere gegevens (minder dan 1200 rijen). Ik probeerde het op het grotere werkblad met (17000 rijen) te gebruiken en het crashte gewoon na het splitsen in 10-12 vellen. Dus probeerden we de originele gegevens op te splitsen in 3 verschillende werkmappen en sloten ons nog steeds af. We hebben Windows 7 en onze computers zijn ook niet zo traag. Stelt u beperkte rijen voor gegevens voor om deze code veilig te gebruiken? Elke suggestie wordt op prijs gesteld.
MieMie
Deze opmerking is gemaakt door de moderator op de site
De code werkte als een tierelier voor kleinere gegevens (minder dan 1200 rijen). Ik probeerde het op het grotere werkblad met (17000 rijen) te gebruiken en het crashte gewoon na het splitsen in 10-12 vellen. Dus probeerden we de originele gegevens op te splitsen in 3 verschillende werkmappen en sloten ons nog steeds af. We hebben Windows 7 en onze computers zijn ook niet zo traag. Stelt u beperkte rijen voor gegevens voor om deze code veilig te gebruiken? Elke suggestie wordt op prijs gesteld. Wat ik niet zeker weet, is: Wat is het maximale aantal rijen dat de macro kan ondersteunen? Ik zou er wat mee kunnen spelen... Het is ergens tussen de 20k en 40k![/quote]
Abel Salomo
Deze opmerking is gemaakt door de moderator op de site
Geconfronteerd met hetzelfde probleem. Code werkt voor bladen waar gegevens minder rijen zijn, maar voor grotere gegevens geeft het een fout weer als "Excel kan deze taak niet voltooien met beschikbare bronnen. Kies minder gegevens of sluit andere toepassingen" (er is geen andere toepassing die tegelijkertijd werkt) De code werkte als een charme voor kleinere gegevens (minder dan 1200 rijen). Ik probeerde het op het grotere werkblad met (17000 rijen) te gebruiken en het crashte gewoon na het splitsen in 10-12 vellen. Dus probeerden we de originele gegevens op te splitsen in 3 verschillende werkmappen en sloten ons nog steeds af. We hebben Windows 7 en onze computers zijn ook niet zo traag. Stelt u beperkte rijen voor gegevens voor om deze code veilig te gebruiken? Elke suggestie wordt op prijs gesteld.
Shrikant
Deze opmerking is gemaakt door de moderator op de site
Je bent mijn held aller tijden! Ik heb hier maanden naar gejaagd zonder geluk. Ik moet dit wekelijks/maandelijks opsplitsen in 147+ werkbladen en ik krijg geen kutools. Wat dat betreft... Ik moet echt leren coderen. :( Maar bedankt!
Bekki
Deze opmerking is gemaakt door de moderator op de site
Hallo, ik heb een blad met 65000 records en 8 verschillende gevallen, dus eigenlijk zou het 80 verschillende vellen moeten genereren. Ik heb geprobeerd deze code uit te voeren, maar het gooit Runtime Error 6 Overflow. Kan deze code worden aangepast om mijn probleem op te lossen? Gelieve ur hulp zal zeer worden gewaardeerd.
ACE
Deze opmerking is gemaakt door de moderator op de site
[quote] Hallo, ik heb een blad met 65000 records en 8 verschillende gevallen, dus eigenlijk zou het 80 verschillende vellen moeten genereren. Ik heb geprobeerd deze code uit te voeren, maar het gooit Runtime Error 6 Overflow. Kan deze code worden aangepast om mijn probleem op te lossen? Gelieve ur hulp zal zeer worden gewaardeerd.Door ACE[/quote] Probeer Dim vcol, i As Integer te veranderen in Dim vcol, i As Long
JD
Deze opmerking is gemaakt door de moderator op de site
Hallo, ik heb geprobeerd DIM vcol in LOng te veranderen en het werkte goed, maar plotseling door een fout is er niet genoeg geheugen om deze actie te voltooien, probeer minder gegevens te gebruiken of andere applicaties te sluiten. Hoewel ik geen andere toepassingen open heb staan. Ik heb iets meer dan 100 k rijen en ongeveer. 16 mb bestandsgrootte. alle hulp zou op prijs worden gesteld. Bedankt Mustafa
Mustafa
Deze opmerking is gemaakt door de moderator op de site
Geweldig stukje code - werkt perfect (als je de variabelen verandert in degene die je spreadsheet nodig heeft)
Croftycooke
Deze opmerking is gemaakt door de moderator op de site
Ik heb een blad met een variabel aantal rijen. Een van de kolommen zijn de data vanaf 2010. De andere kolommen zijn fondsnamen met de NAV-gegevens voor elk fonds tegen de datum. Dus ik wil de kolommen niet opsplitsen in verschillende bladen, ik wil elke FONDSNAAM op een eigen blad splitsen met de NAV-gegevens per einddatum van de maand, niet de dagelijkse data. Kan dit of is dit onmogelijk?
Ahmed Sarheed
Deze opmerking is gemaakt door de moderator op de site
Ik heb een werkblad dat ik gebruik en ik probeer een vba-code te vinden die een accountnaam herkent en de specifieke rij naar een nieuwe werkmap en blad met dezelfde naam kopieert. Kun je helpen?
Tim
Deze opmerking is gemaakt door de moderator op de site
Super goed! De VBA-code werkt, bedankt! Ik heb deze uitvoerwerkbladen nodig in afzonderlijke Excel-bestanden in plaats van werkbladen en er is een fout wanneer ik opsplits in veel werkbladen.
Sterrencor
Deze opmerking is gemaakt door de moderator op de site
Starscor en Tim als je de bladen van het bestand in verschillende bestanden wilt splitsen met behulp van de namen van de rijen, er is een kleine macrocode in dezelfde webpagina die dit doet, zoek gewoon naar "split a workbook to separate Excel files" you zal het vinden. Voeg de code van dat voorbeeld toe aan het einde van deze en verwijder natuurlijk de dubbele end sub en sub en je krijgt voor elk een bestand.
kokoliso
Deze opmerking is gemaakt door de moderator op de site
kan iemand me helpen hoe ik de kolommen in verschillende bladen in dezelfde werkmap tegelijk kan sorteren en ook om duplicaten in verschillende bladen te verwijderen, aangezien ik ongeveer 65 bladen in dezelfde werkmap heb
yash
Deze opmerking is gemaakt door de moderator op de site
dit is zo spannend! Dank u. Ik ben hier al een tijdje naar op zoek.
Theresa
Deze opmerking is gemaakt door de moderator op de site
Uitstekend - bedankt om dit te delen. Verspreidt zelfs markeringen/opmaak naar nieuwe werkbladen!
Frankie
Er zijn nog geen reacties geplaatst
Laad meer
Laat uw commentaar
Posten als gast
×
Beoordeel dit bericht:
0  Personages
Voorgestelde locaties