Rozdeľte hárok programu Excel do viacerých súborov na základe stĺpca pomocou VBA

Máte veľké údaje na hárku programu Excel a potrebujete ho distribuovať do viacerých hárkov na základe niektorých údajov v stĺpci? Je to veľmi základná úloha, ale je časovo náročná.

Mám napríklad tieto údaje. Tieto údaje majú názov stĺpca Dátum, spisovateľ a Názov. V stĺpci Spisovateľ je uvedené meno spisovateľa s príslušným názvom. Chcem dostať údaje každého spisovateľa do samostatných listov.

Aby som to urobil ručne, musím urobiť nasledovné:

Vyfiltrujte jedno meno
Skopírujte filtrované údaje
Pridajte list
Prilepte údaje
Premenujte list
Zopakujte všetkých 5 vyššie uvedených krokov pre každý z nich.

V tomto prípade mám iba tri mená. Predstavte si, že máte stovky názvov. Ako by ste rozdelili údaje do rôznych hárkov? Zaberie to veľa času a vyčerpá to aj vás.
Ak chcete automatizovať vyššie uvedený proces rozdelenia hárka na viacero hárkov, postupujte podľa týchto krokov.

Stlačte kombináciu klávesov Alt+F11. Tým sa otvorí VB Editor pre Excel
Pridajte nový modul
Skopírujte kód pod modul.

 Podrozdelenie do hárkov () 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 (Riadky. Počet, 1). Koniec (xlUp). Riadok Dim je jedinečný ako rozsah Dim clm ako reťazec, clmNo tak dlho pri chybe GoTo handler clm = Application.InputBox („Z ktorého stĺpca chcete vytvárať súbory“ & vbCrLf & „Napr. A, B, C, AB, ZA atď. ") ClmNo = rozsah (clm &" 1 "). Sada stĺpcov uniques = rozsah (clm &" 2: "& clm & lstRow) 'Volanie Odstrániť duplikáty na získanie sady jedinečných mien uniques = RemoveDuplicates (uniques) Zavolajte CreateSheets (uniques, clmNo) s aplikáciou .ScreenUpdating = True .DisplayAlerts = True .AlertBeforeOverwriting = True .Calculation = xlCalculationAutomatic Koniec s listom 1.Activate MsgBox "Well Done!" Ukončiť obslužný nástroj Sub Data.ShowAllData: s aplikáciou .ScreenUpdating = True .DisplayAlerts = True .AlertBeforeOverwriting = True .Calculation = xlCalculationAutomatic Koniec s koncovou podradenou položkou Funkcia 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, Hlavička: = xlNo lstRow = Bunky (riadky. Počet, 1). Koniec (xlUp). Riadok Nastaviť RemoveDuplicates = Rozsah ("A2: A" & lstRow) Koncová funkcia 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). Riadok lstClm = bunky (1, stĺpce. počet). Koniec (xlToLeft). Ladenie stĺpcov. Vytlačiť 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

Keď pobežíš SplitIntoSheets () podľa postupu bude hárok rozdelený do viacerých hárkov podľa daného stĺpca. Na hárok môžete pridať tlačidlo a priradiť mu k tomu toto makro.

Ako to funguje
Vyššie uvedený kód má dva postupy a jednu funkciu. Dva postupy sú SplitIntoSheets (), CreateSheets (jedinečné ako rozsah, clmNo tak dlhé) a jedna funkcia je RemoveDuplicates (uniques As Range) As Range.

Prvý postup je SplitIntoSheets (). Toto je hlavný postup. Tento postup nastaví premenné a Odstrániť duplikáty získať jedinečné názvy z daného stĺpca a potom ich odovzdať CreateSheets na vytváranie listov.

Odstrániť duplikáty trvá jeden argument, ktorý je rozsahom, ktorý obsahuje názov. Odstráni duplikáty od nich a vráti objekt rozsahu, ktorý obsahuje jedinečné názvy.

Teraz CreateSheets sa volá. Na to treba dva argumenty. Najprv jedinečné názvy a za druhé stĺpec č. z ktorého to bude vhodnejšie údaje. Teraz CreateSheets prevezme každé meno od jedinečného a filtruje číslo daného stĺpca podľa každého mena. Skopíruje filtrované údaje, pridá hárok a vloží tam údaje. A vaše údaje sú v priebehu niekoľkých sekúnd rozdelené na rôzne listy.

Súbor si môžete stiahnuť tu.
Rozdeliť na listy

Ako použiť súbor:

- Skopírujte svoje údaje na hárok 1. Uistite sa, že začína od A1.

- Kliknite na tlačidlo Rozdeliť na listy
- Zadajte písmeno stĺpca, od ktorého chcete rozdeliť. Kliknite na Ok.

- Zobrazí sa vám taká výzva. Váš list je rozdelený.

Dúfam, že vám pomohol článok o rozdelení údajov na samostatné listy. Ak máte akékoľvek pochybnosti o tejto alebo o inej funkcii programu Excel, neváhajte sa ich opýtať v sekcii komentárov nižšie.