Cara membagi lembar excel menjadi beberapa lembar kerja berdasarkan baris

Jika Anda memiliki buku kerja Excel dengan banyak lembar kerja, tidak ada cara mudah untuk membagi setiap lembar ini menjadi file Excel terpisah dan menyimpannya secara terpisah

Ini mungkin diperlukan saat Anda menggunakan sheet untuk bulan atau wilayah atau produk/klien yang berbeda dan Anda ingin segera mendapatkan buku kerja terpisah untuk setiap sheet (sebagai file Excel atau sebagai PDF)

Meskipun ada cara manual untuk membagi lembar menjadi buku kerja terpisah dan kemudian menyimpannya, itu tidak efisien dan rawan kesalahan.

Dalam tutorial ini, saya akan memberi Anda kode VBA sederhana yang dapat Anda gunakan untuk dengan cepat (dalam beberapa detik) membagi semua lembar kerja menjadi file terpisah mereka sendiri dan kemudian menyimpannya di folder tertentu.

Tutorial ini Mencakup

Pisahkan Setiap Lembar Kerja Menjadi File Excel Terpisah

Misalkan Anda memiliki buku kerja seperti yang ditunjukkan di bawah ini di mana Anda memiliki lembar kerja untuk setiap bulan

Untuk membagi lembar ini menjadi file Excel terpisah, Anda dapat menggunakan kode VBA di bawah ini

'Code Created by Sumit Bansal from 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

Ada beberapa hal yang perlu Anda pastikan sebelum menggunakan kode VBA di atas

1. Buat folder tempat Anda ingin mendapatkan semua file yang dihasilkan
2. Simpan file Excel utama (yang memiliki semua lembar kerja yang Anda inginkan sebagai file terpisah) di folder ini

Setelah Anda selesai melakukannya, Anda dapat memasukkan kode VBA di atas ke dalam file dan menjalankan kode tersebut

Kode di atas ditulis sedemikian rupa sehingga mengambil lokasi folder menggunakan jalur file (di mana kode dijalankan). Inilah mengapa penting untuk menyimpan file di folder terlebih dahulu dan kemudian menggunakan kode ini

Bagaimana cara kerja kode VBA – Kode di atas menggunakan loop For Next sederhana yang melewati setiap lembar kerja, membuat salinan lembar kerja di buku kerja Excel, lalu menyimpan buku kerja Excel ini di folder yang ditentukan (yang sama dengan yang dimiliki

Di bawah ini adalah langkah-langkah untuk menempatkan kode VBA ini di buku kerja Excel (ini akan sama untuk semua metode lain yang ditampilkan dalam tutorial ini)

Di mana harus meletakkan kode ini?

Di bawah ini adalah langkah-langkah untuk menempatkan kode di Editor Visual Basic agar dapat dieksekusi

• Klik tab Pengembang
  
• Di grup Kode, klik opsi Visual Basic. Ini akan membuka Editor VB. [Anda juga dapat menggunakan pintasan keyboard – ALT + F11]
  
• Di Editor VB, klik kanan salah satu objek buku kerja yang sedang Anda kerjakan
  
• Arahkan kursor ke opsi Sisipkan
• Klik pada Modul. Ini akan memasukkan modul baru
  
• Klik dua kali pada objek Modul. ini akan membuka jendela kode untuk modul
• Salin kode VBA yang disediakan di atas dan tempel di jendela kode modul
  
• Pilih baris mana saja dalam kode dan klik tombol putar hijau di bilah alat untuk menjalankan kode makro VBA
  

Langkah-langkah di atas akan langsung membagi lembar kerja menjadi file Excel terpisah dan menyimpannya. Hanya perlu satu detik jika Anda memiliki lebih sedikit lembar kerja. Jika Anda memiliki banyak, mungkin perlu waktu

Nama setiap file yang disimpan sama dengan nama sheet yang ada di file utama

Karena Anda telah menempatkan kode VBA di buku kerja Excel, Anda harus menyimpannya dengan a. Format XLSM (yang merupakan format berkemampuan makro). Ini akan memastikan makro disimpan dan berfungsi saat Anda membuka file ini selanjutnya

Perhatikan bahwa saya telah menggunakan baris Application. Pembaruan Layar = Salah dan Aplikasi. DisplayAlerts = Salah dalam kode sehingga semuanya terjadi di backend dan tidak melihat sesuatu terjadi di layar Anda. Setelah kode berjalan dan memisahkan sheet dan menyimpannya, kami mengembalikannya ke TRUE

Sebagai praktik terbaik, disarankan untuk membuat salinan cadangan dari file utama (yang berisi lembar yang ingin Anda bagi). Ini akan memastikan Anda tidak kehilangan data jika terjadi kesalahan atau jika Excel memutuskan untuk menjadi lambat atau macet

Pisahkan Setiap Lembar Kerja dan Simpan sebagai PDF Terpisah

Jika Anda ingin membagi lembar kerja dan menyimpannya sebagai file PDF alih-alih file Excel, Anda dapat menggunakan kode di bawah ini

'Code Created by Sumit Bansal from 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.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & "\" & ws.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hal-hal yang perlu Anda pastikan sebelum menggunakan kode ini

1. Buat folder tempat Anda ingin mendapatkan semua file yang dihasilkan
2. Simpan file Excel utama (yang memiliki semua lembar kerja yang Anda inginkan sebagai file terpisah) di folder ini

Kode di atas membagi setiap lembar dalam file Excel dan menyimpannya sebagai PDF di folder yang sama tempat Anda menyimpan file Excel utama

Pisahkan Hanya Lembar Kerja yang Berisi Kata/Frasa menjadi File Excel Terpisah

Jika Anda memiliki banyak lembar dalam buku kerja dan Anda hanya ingin membagi hanya lembar yang memiliki teks tertentu di dalamnya, Anda juga dapat melakukannya

Misalnya, Anda memiliki file Excel di mana Anda memiliki data selama beberapa tahun dan setiap lembar dalam file tersebut memiliki nomor tahun sebagai awalan. Sesuatu seperti yang ditunjukkan di bawah ini

Sekarang, katakanlah Anda ingin membagi semua lembar untuk tahun 2020 dan menyimpannya sebagai file Excel terpisah. Untuk melakukan ini, Anda perlu memeriksa nama setiap lembar kerja dan hanya lembar yang memiliki nomor 2020 yang harus dipisah dan disimpan, dan sisanya tidak boleh disentuh.

Ini dapat dilakukan dengan menggunakan kode makro VBA berikut

'Code Created by Sumit Bansal from 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.Sheets
    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

Dalam kode di atas, saya menggunakan variabel TexttoFind, yang telah ditetapkan ke "2020" di awal

Kode VBA kemudian menggunakan loop For Next di VBA untuk melewati setiap lembar kerja dan kemudian memeriksa nama setiap lembar kerja fungsi INSTR. Fungsi ini memeriksa apakah nama lembar kerja memiliki kata 2020 di dalamnya atau tidak. Jika ya, itu akan mengembalikan nomor posisi di mana ia menemukan teks ini (yaitu 2020 dalam kasus ini)

Dan jika tidak menemukan teks yang kita cari, itu mengembalikan 0

Ini digunakan dengan kondisi IF Then. Jadi jika nama sheet memiliki string teks 2020 di dalamnya, itu akan dipisah dan disimpan sebagai file terpisah. Dan jika tidak memiliki string teks ini, kondisi IF tidak akan terpenuhi dan tidak akan terjadi apa-apa

Anda mungkin juga menyukai tutorial Excel berikut ini

• Bagaimana Membandingkan Dua Lembar Excel
• Cara Mengurangi Ukuran File Excel
• Menggabungkan Data dari Beberapa Buku Kerja di Excel
• Cara Memulihkan File Excel yang Tidak Disimpan [Semua Opsi + Tindakan Pencegahan]
• Cara Membuka File Excel Tertentu Secara Otomatis di Startup
• Cara Membagi Beberapa Baris dalam Sel Menjadi Sel/Kolom Terpisah
• Fungsi Pemisahan VBA Excel
• Cara Mengimpor File XML ke Excel. Konversikan XML ke Excel
• Cara Membagi Layar di Excel (Bandingkan Berdampingan)

BUKU EXCEL GRATIS

Dapatkan 51 Ebook Tips Excel untuk meningkatkan produktivitas Anda dan menyelesaikan pekerjaan lebih cepat

Nama

Surel

YA - KIRIM SAYA EBOOK

6 pemikiran pada “Membagi Setiap Lembar Excel Menjadi File Terpisah (Langkah-demi-Langkah)”

1. Emmalee Kelley Holzheimer

   Saya mencoba membagi dan lembar kerja menjadi 2 lembar masing-masing dengan Nama Scrap Yard, Faircrest, dan Harrison. Saya ingin tahu bagaimana melakukan ini juga, tolong

2. Emmalee Kelley Holzheimer

   Saat saya menyalin VBA Anda untuk memisahkan lembar kerja saya, saya mendapatkan kesalahan di baris pertama. Sub Split Setiap Lembar Kerja () ditampilkan dalam nama kotak makro sebagai Sheet2. SplitEachWorkbook. Vba tidak akan berjalan. Apa yang saya lakukan salah?

   Sub SplitEachWorksheet()
   Redupkan FPath Sebagai String
   Redupkan Teks untuk Menemukan Sebagai String
   TexttoFind = “Tempat Sampah”
   FPath = Aplikasi. ActiveWorkbook. Jalur
   Aplikasi. Pembaruan Layar = Salah
   Aplikasi. Peringatan Tampilan = Salah

   Untuk Setiap ws Dalam Buku Kerja Ini. Seprai
   Jika InStr(1, ws. Nama, TexttoFind, vbBinaryCompare) 0 Kemudian
   ws. Menyalin
   Aplikasi. ActiveWorkbook. Simpan Sebagai Nama File. =FPath & “” & ws. Nama & “. xlsx”
   Aplikasi. ActiveWorkbook. Tutup Salah
   Berakhir jika
   Berikutnya

   Aplikasi. Peringatan Tampilan = Benar
   Aplikasi. Pembaruan Layar = Benar
   Akhir Sub

3. Srinivas

   Jika saya menyalin kode ke buku makro PRIBADI, itu tidak berfungsi. Saya perlu menyalin kode di lembar tertentu dari buku kerja dan mengeksekusi. Mengapa?

   Bagaimana cara membagi spreadsheet Excel menjadi beberapa lembar kerja dengan kriteria?

   Untuk membagi rentang data menjadi beberapa lembar kerja tergantung kolom, harap pilih rentang terlebih dahulu. Kemudian, dengan memilih Beranda > Lembar Kerja > Pisahkan Data , gunakan alat.

   Bagaimana membagi data menjadi beberapa buku kerja berdasarkan kolom di Excel?

   Cara membagi lembar Excel menjadi beberapa lembar kerja .

   Pada Langkah 1 pilih jangkauan Anda

   Pada Langkah 2 pilih satu atau lebih kolom kunci untuk pemisahan

   Pada Langkah 3 pilih tujuan untuk hasil tabel terpisah

   Pada Langkah 4 pilih opsi tambahan. nama lembar kerja, tajuk dan pemformatan

   Bagaimana cara memisahkan spreadsheet Excel menjadi file terpisah?

   Pisahkan buku kerja untuk memisahkan File Excel dengan fitur Pindahkan atau Salin .

   Pilih lembar di bilah tab Lembar, klik kanan, dan pilih Pindahkan atau Salin dari menu konteks. .

   Dalam dialog Pindahkan atau Salin, pilih (buku baru) dari daftar tarik-turun Ke buku, centang opsi Buat salinan, dan klik tombol OK

   Bagaimana cara membagi data menjadi beberapa lembar kerja berdasarkan kolom di Excel tanpa VBA?

   Awalnya, pergi ke Sisipkan Tab>> klik pada Opsi PivotTable. .

   Kemudian, PivotTable dari table atau range Dialog Box akan muncul

   Selanjutnya, pilih Table/Range

   Setelah itu, klik pada Lembar Kerja Baru (merupakan praktik yang baik untuk menempatkan Tabel Pivot ke dalam lembaran baru)

   Terakhir, tekan OK