Membagi 1 File Excel ke Dalam Beberapa File Excel

Adakalanya kita butuh memecah 1 file excel ke dalam beberapa file excel. Jadi informasi yang ada di dalam file excel tersebut dipecah-pecah ke dalam beberapa file excel sesuai kebutuhan. Saya mengalami masalah ini ketika saya harus meng-upload ribuan baris data excel ke dalam sebuah database padahal sekali  upload hanya dapat membaca 50 baris data. Setelah putar sana putar sini, akhirnya saya menggunakan cara di bawah ini pada Microsoft Excel saya.

Pertama-tama, saya akan membagi data yang ada di dalam 1 Sheet, ke dalam beberapa Sheet tapi masih dalam 1 file Excel yang sama. Pada contoh kali ini saya memiliki data yang terdiri dari 1284 baris. Nah data ini akan saya pecah-pecah ke dalam beberapa Sheet dimana 1 Sheet terdiri dari 50 baris data.

Dari Excel, tekan Alt+F11 untuk membuka Visual Basic. Kemudian pilih Insert dan Module untuk membuka modul script VB.

Copy-paste command-command di bawah ini ke dalam modul script VB yang sudah terbuka

Sub SplitData()
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
xTitleId = "AliefKasep"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", xTitleId, 5, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
resizeCount = SplitRow
If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
xRow.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Application.ActiveSheet.Range("A1").PasteSpecial
Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

Jalankan command dengan memilih Run atau menekan F5. Maka kemudian akan muncul pertanyaan mengenai data mana yang hendak dipecah-pecah. Dalam contoh ini saya memilih data dari cell A1 sampai G1284. Hal ini dapat dilakukan dengan memblok cell yang hendak dipilih.

Kemudian akan muncul pertanyaan, pemecahannya hendak dibuat per-berapa baris? Pada contoh ini saya membutuhkan pemecahan data setiap 50 baris.

Setelah itu, command-command VB berjalan dan membuat data yang tadinya terdiri dari 1284 baris terpecah-pecah ke dalam 25 Sheet. Setiap Sheet pada Sheet2 sampai Sheet 27 berisi 50 bari pecahan data dari Sheet1. Sheet1 masih utuh berisikan data awal yang belum dipecah dan masih terdiri dari 1284 baris.

Baiklah, dengan selesainya proses di atas maka pemecahan 1 Sheet ke dalam beberapa Sheet sudah selesai. Selanjutnya, saya ingin memecah Sheet-Sheet ini ke dalam beberapa file Excel yang terpisah. Langkah berikutnya adalah kembali membuka Visual Basic dengan menekan Alt+F11. Kemudian kembali buka modul dan copy-paste command-command di bawah ini.

Sub Splitbook()
'AliefCakep
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Jalankan command dengan menekan F5 dan Sheet-Sheet pada file tersebut sudah terpecah-pecah ke dalam beberapa file excel yang terpisah. Judul dan isi dari masing-masing file tersebut akan sama persis dengan Sheet-Sheet yang terdapat pada file awal.

Akhir kata, semoga bermanfaat (^_^).

 

18 thoughts on “Membagi 1 File Excel ke Dalam Beberapa File Excel

  1. Dear penulis,
    Mas, saya mau tanya kalau dengan pemisahan ini nantinya kalo saya update data di master data yg all (data awal yang dipisah-pisah) apakah otomatis update ke data per sheet ya?
    Terimakasih.

    Suka

  2. klo untuk selnya bisa ngga ga dari cel a1 tapi custom dari sel dibawahnya
    misal dari A5 tp saya juga ada headernya bisa ngga headernya sama

    Suka

    • Kalau memilih cell yang mana saja yang mau dimasukkan bisa. Setelah scrip pertama dirunning ada pilihan cell mana saja yanv hendak dipecah-pecah. Tapi kalau header sama di setiap file pecahan, tidak bisa.

      Suka

    • Bisa, kalau hasil pecahannya .mau dibuat csv, coba running ini:
      Sub Splitbook()
      ‘Updateby20140612
      Dim xPath As String
      xPath = Application.ActiveWorkbook.Path
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      For Each xWs In ThisWorkbook.Sheets
      xWs.Copy
      Application.ActiveWorkbook.SaveAs Filename:=xPath & “\” & xWs.Name & “.csv”
      Application.ActiveWorkbook.Close False
      Next
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
      End Sub

      Suka

      • nah ini mas, tadi sudah saya coba ganti file tipenya, tapi ketika file csv dibuka di aplikasi notepad isinya jadi bahasa robot haha

        Suka

  3. Misi mas, saya mau tanya. Waktu save dengan extensi CSV itu bisa jadi csv delimitied ngga?
    Sy coba pake yang langsung tembak .csv jatoh nya bukan delimitied.
    Trimakasih.

    Suka

    • Coba pakai ini:
      Sub Splitbook() ‘AliefCakep Dim xPath As String xPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each xWs In ThisWorkbook.Sheets xWs.Copy Application.ActiveWorkbook.SaveAs Filename:=xPath & “\” & xWs.Name & “.xls” Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

      Suka

  4. Halo kak, terima kasih sharing nya yang sangat jelas. Tapi saya ingin bertanya, kalau saya ingin pisah data pada 1 file excel saya, berdasarkan kategori yang saya tentukan, apakah bisa?

    Misal di dalam1 file excel saya, ada 5 row Apel, 10 row Jeruk, 12 row Anggur. Nah saya ingin memisahkan data Apel ke 1 file tersendiri, Jeruk 1 file sendiri dan Anggur 1 file tersendiri.

    Mohon di bantu pencerahannya kak. Atau apakah ada rekomendasi tempat belajar untuk rumus macro kah? Terima kasih.

    Suka

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout /  Ubah )

Foto Google

You are commenting using your Google account. Logout /  Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout /  Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout /  Ubah )

Connecting to %s