Разделете всеки лист на Excel на отделни файлове (стъпка по стъпка)

Гледайте видео - Как да разделите всеки лист на Excel на отделни файлове

Ако имате работна книга на Excel с много работни листове, няма лесен начин да разделите всеки от тези листове на отделни Excel файлове и да запишете отделно.

Това може да е необходимо, когато правите листове за различни месеци или региони или продукти/клиенти и искате бързо да получите отделна работна книга за всеки лист (като файл на Excel или като PDF файлове).

Въпреки че има ръчен начин за разделяне на листове в отделни работни книги и след това да ги запишете, това е неефективно и склонно към грешки.

В този урок ще ви дам прост VBA код, който можете да използвате за бързо (за няколко секунди) разделяне на всички работни листове на отделни файлове и след това да ги запишете във всяка определена папка.

Разделете всеки работен лист в отделен Excel файл

Да предположим, че имате работна книга, както е показано по -долу, където имате работен лист за всеки месец.

За да разделите тези листове в отделен Excel файл, можете да използвате кода по -долу VBA:

'Код, създаден от Sumit Bansal от trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False За всеки ws В ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.Save Име на файл: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

Има няколко неща, които трябва да се уверите, преди да използвате горния код VBA:

1. Създайте папка, където искате да получите всички получени файлове.
2. Запазете основния файл на Excel (който има всички работни листове, които искате като отделни файлове) в тази папка.

След като направите това, можете да поставите горния код на VBA във файла и да стартирате кода.

Горният код е написан по начин, който улавя местоположението на папката, като използва пътя на файла (в който кодът се изпълнява). Ето защо е важно първо да запишете файла в папката и след това да използвате този код.

Как работи кодът VBA - Горният код използва прост цикъл For Next, който преминава през всеки работен лист, създава копие на работния лист в работна книга на Excel и след това записва тази работна книга на Excel в посочената папка (която е същата, която има основния файл с всички листове).

По -долу са стъпките за поставяне на този VBA код в работната книга на Excel (те ще бъдат еднакви за всички други методи, показани в този урок):

Къде да сложа този код?

По -долу са стъпките за поставяне на кода в редактора на Visual Basic, където той може да бъде изпълнен:

• Щракнете върху раздела Разработчик.
• В групата Код щракнете върху опцията Visual Basic. Това ще отвори VB Editor. [Можете също да използвате клавишната комбинация - ALT + F11]
• В VB Editor щракнете с десния бутон върху някой от обектите на работната книга, върху която работите.
• Задръжте курсора на мишката върху опцията Вмъкване
• Кликнете върху Модул. Това ще постави нов модул
• Щракнете двукратно върху обекта Module. това ще отвори прозореца с код за модула
• Копирайте предоставения по -горе VBA код и го поставете в прозореца с код на модула.
• Изберете който и да е ред в кода и щракнете върху зеления бутон за възпроизвеждане в лентата с инструменти, за да стартирате VBA макрокода.

Горните стъпки незабавно ще разделят работните листове на отделни Excel файлове и ще ги запазят. Това отнема само секунда, ако имате по -малък брой работни листове. В случай, че имате много, може да отнеме известно време.

Името на всеки записан файл е същото като това на името на листа, което е имало в основния файл.

Тъй като сте поставили VBA код в работната книга на Excel, трябва да запишете това с .XLSM формат (който е формат с активирани макроси). Това ще гарантира, че макросът е запазен и работи, когато отворите този файл след това.

Обърнете внимание, че съм използвал линиите Application.ScreenUpdating = False и Application.DisplayAlerts = False в кода, така че всичко да се случва в задната част и да не виждате нещата, които се случват на екрана ви. След като кодът стартира и раздели листовете и ги запише, ние ги връщаме обратно на ИСТИНА.

Като най -добра практика се препоръчва да се създаде резервно копие на основния файл (който съдържа листовете, които искате да разделите). Това ще гарантира, че няма да загубите данните си в случай, че нещо се обърка или ако Excel реши да забави или да се срине.

Разделете всеки работен лист и запишете като отделен PDF файл

В случай, че искате да разделите работните листове и да ги запишете като PDF файлове вместо файловете на Excel, можете да използвате кода по -долу:

„Код, създаден от Sumit Bansal от trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False За всеки ws В ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.Export Тип: = xlTypePDF, Име на файл: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

Нещата, които трябва да сте сигурни, преди да използвате този код:

1. Създайте папка, където искате да получите всички получени файлове.
2. Запазете основния файл на Excel (който съдържа всички работни листове, които искате като отделни файлове) в тази папка.

Горният код разделя всеки лист във файла Excel и го записва като PDF в същата папка, където сте записали основния файл на Excel.

Разделете само тези работни листове, които съдържат дума/фраза в отделни файлове на Excel

В случай, че имате много листове в работна книга и искате да разделите само тези листове, които имат определен текст в нея, можете да направите и това.

Да предположим например, че имате Excel файл, в който давате данни за няколко години и всеки лист във файла има номера на годината като префикс. Нещо, както е показано по -долу:

Да предположим, че искате да разделите всички листове за 2021-2022 г. и да ги запишете като отделни Excel файлове. За да направите това, трябва по някакъв начин да проверите името на всеки работен лист и само тези листове, които имат номер 2021-2022, трябва да бъдат разделени и запазени, а останалите трябва да бъдат оставени недокоснати.

Това може да стане с помощта на следния VBA макро код:

'Код, създаден от Sumit Bansal от trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String Dim TexttoFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False За всеки ws в тази книга. Листове If InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 Тогава ws.Copy Application.ActiveWorkbook.SaveAs Име на файла: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

В горния код използвах променлива TexttoFind, която в началото беше присвоена на „2020“.

След това кодът VBA използва цикъла For Next във VBA, за да премине през всеки работен лист и след това да провери името на всеки INSTR функция на всеки работен лист. Тази функция проверява дали името на работния лист съдържа думата 2021-2022 или не. Ако го направи, той ще върне номер на позиция, където намери този текст (който е 2021-2022 в този случай).

И ако не намери текста, който търсим, връща 0.

Това се използва с условието IF then. Така че, ако името на лист има текстов низ 2021-2022 в него, то ще бъде разделено и записано като отделен файл. И ако няма този текстов низ, условието IF няма да бъде изпълнено и нищо няма да се случи.