Cookies helpen ons onze services te leveren. Door gebruik te maken van onze diensten, gaat u akkoord met ons gebruik van cookies.
Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe gegevens in meerdere werkbladen te splitsen op basis van kolom in Excel?

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

doc splitsen gegevens op kolommen 1

Gegevens splitsen in meerdere werkbladen op basis van kolom met VBA-code

Gegevens splitsen in meerdere werkbladen op basis van kolom met Kutools voor Excel


Gegevens splitsen in meerdere werkbladen op basis van specifieke kolom- of rijen tellen in werkblad:

Als u een groot werkblad in meerdere vellen wilt splitsen op basis van specifieke kolomgegevens of aantal rijen, de Kutools for Excel's Gegevens splitsen functie kan u helpen om deze taak snel en gemakkelijk op te lossen.

doc splitsen gegevens op kolommen 6

Kutools for Excel: met meer dan 200 handige Excel-add-ins, gratis om zonder beperking in 60-dagen te proberen. Download en gratis proef nu!


Gegevens splitsen in meerdere werkbladen op basis van kolom met VBA-code


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

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

2. Klikken bijvoegsel > moduleen plak de volgende code in het modulevenster.

Sub parse_data()
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
vcol = 1        
Set ws = Sheets("Master sheet")        
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:C1"            
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 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
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

Notes: In de bovenstaande code:

  • vcol = 1 , het nummer 1 is het kolomnummer waarop u de gegevens wilt splitsen op basis van.
  • Set ws = Sheets ("Master sheet"), Hoofdpagina is de bladnaam waarop u deze code wilt toepassen.
  • title = "A1: C1" , A1: C1 is het bereik van de titel.

Allemaal variabelen, je kunt ze als je behoefte veranderen.

3. Druk vervolgens op F5 sleutel om de code uit te voeren, alle gegevens in het actieve werkblad worden opgesplitst in meerdere werkbladen door de kolomwaarde. En de gesplitste werkbladen worden genoemd met de gesplitste celnamen. Zie screenshot:

doc splitsen gegevens op kolommen 2

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


Gegevens splitsen in meerdere werkbladen op basis van kolom met Kutools voor Excel

Als een Excel-beginner is deze lange VBA-code enigszins moeilijk voor ons, en de meesten van ons weten zelfs niet hoe de code moet worden aangepast als onze behoefte.

Hier zal ik je een multifunctioneel hulpmiddel voorstellen--Kutools for Excel, het is Gegevens splitsen hulpprogramma kan niet alleen helpen bij het splitsen van gegevens in meerdere werkbladen op basis van een kolom, maar kan ook gegevens splitsen op basis van het aantal rijen.

Kutools for Excel : met meer dan 300 handige Excel-invoegtoepassingen, gratis om zonder beperking te proberen in 60-dagen.

Als je hebt geïnstalleerd Kutools for Excel, alsjeblieft als volgt doen:

1. Selecteer het gegevensbereik dat u wilt splitsen.

2. Klikken Kutools Plus > Werkblad > Gegevens splitsenzie screenshot:

doc splitsen gegevens op kolommen 3

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

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

2). Vervolgens kunt u de gesplitste werkbladnamen opgeven onder de Naam nieuwe werkbladen sectie, geeft u de regels voor werkbladnamen op vanuit de Reglement vervolgkeuzelijst, kunt u de Voorvoegsel or Achtervoegsel ook voor de bladnamen.

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

doc splitsen gegevens op kolommen 4

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

doc splitsen gegevens op kolommen 5

Klik om Kutools voor Excel te downloaden en nu gratis uitproberen!


Gegevens splitsen in meerdere werkbladen op basis van kolom met Kutools voor Excel

Kutools for Excel bevat meer dan handige Excel-gereedschappen van 300. Gratis om te proberen zonder beperking in 60-dagen. Download de gratis proefversie nu!


Gerelateerd artikel:

Hoe gegevens in meerdere werkbladen op rijen te splitsen?



Aanbevolen productiviteitstools

Office Tab

gouden ster1 Breng handige tabbladen naar Excel en andere Office-software, net zoals Chrome, Firefox en nieuwe Internet Explorer.

Kutools for Excel

gouden ster1 Verbazingwekkend! Verhoog uw productiviteit in 5 minuten. Heb geen speciale vaardigheden nodig, bespaar elke dag twee uur!

gouden ster1 300 Nieuwe functies voor Excel, maak Excel veel gemakkelijk en krachtig:

  • Cel / rijen / kolommen samenvoegen zonder gegevens te verliezen.
  • Combineer en consolideer meerdere bladen en werkmappen.
  • Bereiken vergelijken, meerdere bereiken kopiëren, tekst converteren naar datum, eenheid en valuta omrekenen.
  • Count by Colors, paging-subtotalen, geavanceerde sortering en superfilter,
  • Meer Selecteren / Invoegen / Wissen / Tekst / Formaat / Link / Commentaar / Werkmappen / Werkbladen Hulpmiddelen ...

Schermopname van Kutools voor Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jason · 1 months ago
    This formula is great, works perfectly for me.
    I want to split out data based on a location, which is in column 1. Which this does.
    However, is it possible to also split out based on column 2, for example. Built and Not Built. So a secondary condition also?
  • To post as a guest, your comment is unpublished.
    jose · 1 months ago
    can someone help please im using this but i keep getting to many columns. i have to keep deleting rows every time i use this.

    This is what im using


    Sub parse_data()
    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
    vcol = 1
    Set ws = Sheets("sheet1")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:AN1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 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
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    Sheets(myarr(i) & "").Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
    End Sub
  • To post as a guest, your comment is unpublished.
    avinash · 1 months ago
    Thanks for VBA, it works great. In addition to that question, I have column which is dependent. So when i select some value my column values get changed hence I need vba solution to replace earlier split worksheet to replace with new value of columns. Can anyone help me out.?

    Thanks in advance
  • To post as a guest, your comment is unpublished.
    JP Tontegode · 1 months ago
    Is there a way to have the macro create a separate spreadsheet for each new tab instead of just adding a tab into the current worksheet? Thanks!
  • To post as a guest, your comment is unpublished.
    Sebastian · 1 months ago
    Effectively, this code does work great. I wrote an additional code so that I could get subtotal on certain columns but it has not worked great. So I tried running the subtotals from the master list, but afterwards when I use this macro it create a whole new tab for the Grand total row. So I am getting two two tabs per split. the first one is fine because it splits with the grand totals, but then creates a second one with just the grand total row. Any help as to how to modify this.
    This is the code that I am currently using:
    Sub parse_data()
    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
    vcol = 3
    Set ws = Sheets("Master sheet")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:R1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 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
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    Sheets(myarr(i) & "").Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
    End Sub