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.
Splits gegevens in meerdere werkbladen op basis van kolom met VBA-code
Splits gegevens in meerdere werkbladen op basis van kolom met Kutools for 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
Dim xWS 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
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.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:
4. En klik dan OK knop, en selecteer in het tweede promptvenster de kolomgegevens die u wilt splitsen op basis van, zie screenshot:
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:
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 for 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 Koetools Plus > Werkblad > Gegevens splitsen, zie screenshot:
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 Toevoeging ook voor de bladnamen.
3). Klik op de OK knop. Zie screenshot:
4. Nu worden de gegevens opgesplitst in meerdere werkbladen in een nieuwe werkmap.
Klik om te downloaden Kutools for Excel en gratis proef Nu!
Splits gegevens in meerdere werkbladen op basis van kolom met Kutools for 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?
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools for Excelen ervaar efficiëntie als nooit tevoren. Kutools for Excel Biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab 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 honderden muisklikken voor u elke dag!
