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 SubAda beberapa hal yang perlu Anda pastikan sebelum menggunakan kode VBA di atas
- Buat folder tempat Anda ingin mendapatkan semua file yang dihasilkan
- 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
- Buat folder tempat Anda ingin mendapatkan semua file yang dihasilkan
- 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 SubDalam 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)”
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
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 = SalahUntuk 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
BerikutnyaAplikasi. Peringatan Tampilan = Benar
Aplikasi. Pembaruan Layar = Benar
Akhir SubSrinivas
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 AndaPada Langkah 2 pilih satu atau lebih kolom kunci untuk pemisahanPada Langkah 3 pilih tujuan untuk hasil tabel terpisahPada Langkah 4 pilih opsi tambahan. nama lembar kerja, tajuk dan pemformatanBagaimana 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 OKBagaimana 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 munculSelanjutnya, pilih Table/RangeSetelah itu, klik pada Lembar Kerja Baru (merupakan praktik yang baik untuk menempatkan Tabel Pivot ke dalam lembaran baru)Terakhir, tekan OK