Rozdělte list aplikace Excel na více souborů podle sloupce pomocí VBA

Máte velká data na listu Excelu a potřebujete jej distribuovat na více listů na základě některých dat ve sloupci? Tento velmi základní úkol, ale časově náročný.

Mám například tato data. Tato data mají sloupec s názvem Datum, spisovatel a Titul. Sloupec Spisovatel má jméno autora příslušného titulu. Chci dostat data každého spisovatele do samostatných listů.

Chcete -li to provést ručně, musím provést následující:

Filtrujte jedno jméno
Zkopírujte filtrovaná data
Přidejte list
Vložte data
Přejmenujte list
Opakujte všech výše uvedených 5 kroků pro každý.

V tomto příkladu mám pouze tři jména. Představte si, kdybyste měli stovky jmen. Jak byste rozdělili data na různé listy? Zabere to spoustu času a také vás to vyčerpá.
Chcete -li automatizovat výše uvedený proces rozdělení listu na více listů, postupujte takto.

Stiskněte Alt+F11. Tím se otevře VB Editor pro Excel
Přidat nový modul
Zkopírujte pod kód v modulu.

 Dílčí rozdělení do listů () With Application .ScreenUpdating = False .DisplayAlerts = False End With ThisWorkbook.Activate Sheet1.Activate 'clearing filter if any On Error Resume Next Sheet1.ShowAllData On Error GoTo 0 Dim lsrClm As Long Dim lstRow As Long' countting last used row lstRow = Cells (Rows.Count, 1). End (xlUp) .Row Dim uniques As Range Dim clm As String, clmNo As Long On Error GoTo handler clm = Application.InputBox ("From which column you want create files" & vbCrLf & "Např. A, B, C, AB, ZA atd. ") ClmNo = Range (clm &" 1 "). Column Set uniques = Range (clm &" 2: "& clm & lstRow) 'Calling Remove Duplicates to get Unique Names Set uniques = RemoveDuplicates (uniques) Zavolejte CreateSheets (uniques, clmNo) pomocí aplikace .ScreenUpdating = True .DisplayAlerts = True .AlertBeforeOverwriting = True .Calculation = xlCalculationAutomatic Konec s Sheet1.Activate MsgBox "Well Done!" Ukončete obslužnou rutinu Sub Data.ShowAllData: s aplikací .ScreenUpdating = True .DisplayAlerts = True .AlertBeforeOverwriting = True .Calculation = xlCalculationAutomatic Konec s End Sub Funkce RemoveDuplicates (uniques As Range) As Range ThisWorkbook.Activate Sheets.Add On Error Resume Next ActiveSheet.Name = "uniques" Sheets ("uniques"). Activate On Error GoTo 0 uniques.Copy Cells (2, 1) .Activate ActiveCell.PasteSpecial xlPasteValues Range ("A1") .Value = "uniques" Dim lstRow As Long lstRow = Cells (Rows.Count, 1) .End (xlUp) .Row Range ("A2: A" & lstRow) .Select ActiveSheet.Range (Selection.Address) .RemoveDuplicates Columns : = 1, Záhlaví: = xlNo lstRow = Buňky (Rows.Count, 1). End (xlUp) .Row Set RemoveDuplicates = Range ("A2: A" & lstRow) End Function Sub CreateSheets (uniques As Range, clmNo As Long) Dim lstClm as Long Dim lstRow As Long for each unique In uniques Sheet1.Activate lstRow = Cells (Rows.Count, 1) .End (xlUp) .Row lstClm = Cells (1, Columns.Count) .End (xlToLeft) .Column Dim dataSet As Range Set dataSet = Range (Cells (1, 1), Cells (lstRow, lstClm)) dataSet.AutoFilter field: = clmNo, Criteria1: = unique.Value lstRow = Cells (Rows.Count, 1) .End ( xlUp) .Row lstClm = Cells (1, Columns.Count) .End (xlToLeft) .Column Debug.Print lstRow; lstClm Set dataSet = Range (Cells (1, 1), Cells (lstRow, lstClm)) dataSet.Copy Sheets.Add ActiveSheet.Name = unique.Value2 ActiveCell.PasteSpecial xlPasteAll Next unique End Sub

Až poběžíš SplitIntoSheets () podle postupu bude list rozdělen na více listů podle daného sloupce. Můžete přidat tlačítko na list a přiřadit mu toto makro.

Jak to funguje
Výše uvedený kód má dva postupy a jednu funkci. Jsou dva postupy SplitIntoSheets (), CreateSheets (uniques As Range, clmNo As Long) a jedna funkce je RemoveDuplicates (uniques As Range) As Range.

První postup je SplitIntoSheets (). Toto je hlavní postup. Tento postup nastavuje proměnné a Odebrat duplikáty Chcete -li získat jedinečné názvy z daného sloupce a poté je předáte do CreateSheets pro vytváření listů.

Odebrat duplikáty bere jeden argument, který je rozsah, který obsahuje název. Odstraní duplikáty od nich a vrátí objekt rozsahu, který obsahuje jedinečná jména.

Nyní CreateSheets je nazýván. Chce to dva argumenty. Nejprve jedinečné názvy a za druhé sloupec č. ze kterého to bude fitler data. Nyní CreateSheets přebírá každé jméno od unikátů a filtruje číslo daného sloupce podle každého jména. Zkopíruje filtrovaná data, přidá list a data tam vloží. A vaše data jsou během několika sekund rozdělena na jiný list.

Soubor si můžete stáhnout zde.
Rozdělit na listy

Jak soubor použít:

- Zkopírujte svá data na List1. Ujistěte se, že začíná od A1.

- Klikněte na tlačítko Rozdělit na listy
- Zadejte písmeno sloupce, od kterého chcete rozdělit. Klikněte na Ok.

- Zobrazí se taková výzva. Váš list je rozdělen.

Doufám, že vám článek o rozdělení dat na samostatné listy pomohl. Máte -li jakékoli pochybnosti o této nebo jakékoli jiné funkci aplikace Excel, neváhejte se zeptat v níže uvedené sekci komentáře.