Obejrzyj wideo - jak podzielić każdy arkusz Excela na osobne pliki
Jeśli masz skoroszyt programu Excel z wieloma arkuszami roboczymi, nie ma łatwego sposobu na podzielenie każdego z tych arkuszy na osobne pliki programu Excel i zapisanie go osobno.
Może to być potrzebne, gdy tworzysz arkusze dla różnych miesięcy lub regionów lub produktów/klientów i chcesz szybko uzyskać osobny skoroszyt dla każdego arkusza (jako plik Excel lub jako PDF).
Chociaż istnieje ręczny sposób dzielenia arkuszy na osobne skoroszyty, a następnie ich zapisywania, jest to nieefektywne i podatne na błędy.
W tym samouczku dam ci prosty kod VBA, którego możesz użyć, aby szybko (w ciągu kilku sekund) podzielić wszystkie arkusze na osobne pliki, a następnie zapisać je w dowolnym określonym folderze.
Podziel każdy arkusz na osobny plik Excel
Załóżmy, że masz skoroszyt, jak pokazano poniżej, w którym masz arkusz roboczy na każdy miesiąc.
Aby podzielić te arkusze na osobny plik Excel, możesz użyć poniższego kodu VBA:
'Kod utworzony przez Sumit Bansal z trumpexcel.com Sub SplitEachWorksheet() Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Jest kilka rzeczy, które musisz upewnić się przed użyciem powyższego kodu VBA:
- Utwórz folder, w którym chcesz pobrać wszystkie wynikowe pliki.
- Zapisz główny plik programu Excel (który zawiera wszystkie arkusze, które chcesz jako osobne pliki) w tym folderze.
Gdy już to zrobisz, możesz umieścić powyższy kod VBA w pliku i uruchomić kod.
Powyższy kod jest napisany w taki sposób, że pobiera lokalizację folderu za pomocą ścieżki pliku (w którym kod jest uruchamiany). Dlatego ważne jest, aby najpierw zapisać plik w folderze, a następnie użyć tego kodu.
Jak działa kod VBA - Powyższy kod wykorzystuje prostą pętlę For Next, która przechodzi przez każdy arkusz, tworzy kopię arkusza w skoroszycie programu Excel, a następnie zapisuje ten skoroszyt programu Excel w określonym folderze (czyli tym samym, w którym znajduje się plik główny ze wszystkimi pościel).
Poniżej znajdują się kroki, aby umieścić ten kod VBA w skoroszycie programu Excel (będą one takie same dla wszystkich innych metod pokazanych w tym samouczku):
Gdzie umieścić ten kod?
Poniżej znajdują się kroki, aby umieścić kod w Edytorze Visual Basic, gdzie można go wykonać:
- Kliknij kartę Deweloper.
- W grupie Kod kliknij opcję Visual Basic. Spowoduje to otwarcie edytora VB. [Możesz również użyć skrótu klawiaturowego - ALT + F11]
- W Edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, nad którym pracujesz.
- Najedź kursorem na opcję Wstaw
- Kliknij Moduł. Spowoduje to wstawienie nowego modułu
- Kliknij dwukrotnie obiekt Module. otworzy to okno kodu dla modułu
- Skopiuj podany powyżej kod VBA i wklej go w oknie kodu modułu.
- Wybierz dowolny wiersz w kodzie i kliknij zielony przycisk odtwarzania na pasku narzędzi, aby uruchomić kod makra VBA.
Powyższe kroki natychmiast podzieliłyby arkusze na osobne pliki Excela i zapisały je. Zajmuje to tylko sekundę, jeśli masz mniej arkuszy roboczych. Jeśli masz dużo, może to zająć trochę czasu.
Nazwa każdego zapisanego pliku jest taka sama, jak nazwa arkusza, który miał w pliku głównym.
Ponieważ umieściłeś kod VBA w skoroszycie programu Excel, musisz go zapisać w formacie .XLSM (który jest formatem obsługującym makra). Zapewni to, że makro zostanie zapisane i będzie działać po następnym otwarciu tego pliku.
Zauważ, że użyłem linii Application.ScreenUpdating = Fałsz oraz Application.DisplayAlerts = False w kodzie, aby wszystko działo się w backendzie i nie widziało, co dzieje się na ekranie. Gdy kod zostanie uruchomiony, podzieli arkusze i je zapisze, przywrócimy je z powrotem do PRAWDA.
W ramach najlepszej praktyki zaleca się utworzenie kopii zapasowej pliku głównego (zawierającego arkusze, które chcesz podzielić). Zapewni to, że nie utracisz danych w przypadku, gdy coś pójdzie nie tak lub jeśli program Excel zdecyduje się zwolnić lub zawiesić.
Podziel każdy arkusz i zapisz jako oddzielny plik PDF
Jeśli chcesz podzielić arkusze i zapisać je jako pliki PDF zamiast plików Excel, możesz użyć poniższego kodu:
'Kod utworzony przez Sumit Bansal z trumpexcel.com Sub SplitEachWorksheet() Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.ExportAsFixed Wpisz:=xlTypePDF, Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Rzeczy, które musisz upewnić się przed użyciem tego kodu:
- Utwórz folder, w którym chcesz pobrać wszystkie wynikowe pliki.
- Zapisz główny plik programu Excel (który zawiera wszystkie arkusze, które chcesz jako osobne pliki) w tym folderze.
Powyższy kod podzielił każdy arkusz w pliku Excel i zapisz go jako plik PDF w tym samym folderze, w którym zapisałeś główny plik Excel.
Podziel tylko te arkusze robocze, które zawierają słowo/frazę na oddzielne pliki Excela
Jeśli masz dużo arkuszy w skoroszycie i chcesz podzielić tylko te arkusze, które zawierają określony tekst, możesz to również zrobić.
Załóżmy na przykład, że masz plik programu Excel, w którym dane z wielu lat, a każdy arkusz w pliku ma numer roku jako prefiks. Coś, jak pokazano poniżej:
Załóżmy teraz, że chcesz podzielić wszystkie arkusze na lata 2021-2022 i zapisać je jako osobne pliki Excela. Aby to zrobić, musisz jakoś sprawdzić nazwę każdego arkusza roboczego i tylko te arkusze, które mają numer 2021-2022, powinny zostać podzielone i zapisane, a reszta powinna pozostać nietknięta.
Można to zrobić za pomocą następującego kodu makra VBA:
'Kod utworzony przez Sumit Bansal z trumpexcel.com Sub SplitEachWorksheet() Dim FPath As String Dim TexttoFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ThisWorkbook. Arkusze If InStr(1, ws.Name, TexttoFind, vbBinaryCompare) 0 Then ws.Copy Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
W powyższym kodzie użyłem zmiennej TexttoFind, która na początku została przypisana do „2020”.
Kod VBA następnie używa pętli For Next w VBA, aby przejść przez każdy arkusz, a następnie sprawdzić nazwę każdej funkcji INSTR arkusza roboczego. Ta funkcja sprawdza, czy nazwa arkusza zawiera słowo 2021-2022, czy nie. Jeśli tak, zwróci numer pozycji, w której znajdzie ten tekst (w tym przypadku 2021-2022).
A jeśli nie znajdzie szukanego tekstu, zwraca 0.
Jest używany z warunkiem JEŻELI Wtedy. Jeśli więc nazwa arkusza zawiera ciąg tekstowy 2021-2022, zostanie on podzielony i zapisany jako osobny plik. A jeśli nie ma tego ciągu tekstowego, warunek JEŻELI nie zostanie spełniony i nic się nie stanie.
