Cara Memisahkan Data Excel Menjadi Beberapa File Menggunakan VBA

Mengelola data dalam file Excel sering kali menghadirkan tantangan, terutama jika data tersebut perlu dipisah berdasarkan kategori tertentu. Misalnya, kita punya daftar siswa dari berbagai kelas dalam satu file, dan ingin membaginya menjadi file terpisah untuk masing-masing kelas. Daripada melakukannya secara manual, kita bisa menggunakan VBA (Visual Basic for Applications) untuk mengotomatisasi proses ini.

Dalam artikel ini, kita akan membahas cara memisahkan data dalam Excel menjadi beberapa file berdasarkan kolom tertentu, lengkap dengan:

• Contoh kasus nyata,
• Script VBA sederhana dan lanjutan,
• Penjelasan kode baris demi baris.

Contoh Kasus

Misalkan kita memiliki data seperti berikut ini:

Nama Siswa	Kelas	Alamat	Nilai
Santi	X-1	Ponjanan Timur	45
Dewi	X-2	Batu Bintang	77
Maisaroh	X-1	Sotabar	65
Alvaro	X-3	Ponjanan Timur	78
Arjun	XI-4	Waru Barat	77

Dalam tabel ini, kolom "Kelas" menjadi acuan pemisahan file.

Pendekatan Sederhana dengan VBA

Untuk pemula, kita bisa menggunakan script sederhana. Berikut kodenya:

Sub SplitDataByKelas_Sederhana()
    Dim wsSource As Worksheet
    Dim wbNew As Workbook
    Dim wsNew As Worksheet
    Dim rngData As Range
    Dim rngUnique As Range
    Dim cell As Range
    Dim kelasName As String
    Dim lastRow As Long
    Dim lastCol As Long

    Set wsSource = ThisWorkbook.Sheets(1)
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
    Set rngData = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, lastCol))

    wsSource.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsSource.Range("Z1"), Unique:=True
    Set rngUnique = wsSource.Range("Z2:Z" & wsSource.Cells(wsSource.Rows.Count, "Z").End(xlUp).Row)

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each cell In rngUnique
        kelasName = cell.Value
        Set wbNew = Workbooks.Add
        Set wsNew = wbNew.Sheets(1)
        rngData.Rows(1).Copy Destination:=wsNew.Range("A1")
        rngData.AutoFilter Field:=2, Criteria1:=kelasName
        rngData.SpecialCells(xlCellTypeVisible).Copy Destination:=wsNew.Range("A2")
        wsSource.AutoFilterMode = False
        wbNew.SaveAs Filename:=ThisWorkbook.Path & "\Kelas_" & kelasName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        wbNew.Close SaveChanges:=False
    Next cell

    wsSource.Range("Z:Z").ClearContents
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    MsgBox "Selesai memisahkan data!"
End Sub

Penjelasan Setiap Bagian Kode

• Definisi Variabel: Menyiapkan semua variabel yang dibutuhkan untuk worksheet, workbook baru, dan range data.
• Menentukan Worksheet dan Data: Menentukan lokasi data, mencari baris dan kolom terakhir agar range data akurat.
• Membuat Daftar Unik: Menggunakan Advanced Filter untuk mengambil daftar kelas yang unik tanpa duplikasi.
• Loop untuk Membuat File Per Kelas: Membuat file baru untuk setiap kelas, menyalin header, filter data per kelas, dan menyimpannya ke file baru.
• Bersih-bersih: Menghapus kolom bantu dan mengaktifkan kembali update layar dan peringatan Excel.
• Notifikasi: Memberi tahu bahwa proses selesai melalui pop-up pesan.

Pendekatan Advance: Lebih Dinamis dan User-Friendly

Kalau ingin proses lebih fleksibel, aman, dan interaktif, kita bisa menggunakan versi lanjutan dari script, yaitu:

• Memilih kolom pemisah lewat input box.
• Memilih folder tujuan untuk file output.
• Menampilkan status progress.

Berikut kode VBA dengan pendekatan lebih dinamis.)

  Sub SplitDataPerKelas_Advanced()
    Dim wsSource As Worksheet
    Dim wbNew As Workbook
    Dim wsNew As Worksheet
    Dim rngData As Range
    Dim rngUnique As Range
    Dim cell As Range
    Dim colKelas As Range
    Dim folderPath As String
    Dim kelasName As String
    Dim lastRow As Long
    Dim lastCol As Long
    Dim fieldNumber As Integer
    Dim copiedCount As Integer
    Dim totalUnique As Integer
    Dim i As Integer

    ' Pilih kolom acuan
    fieldNumber = Application.InputBox("Masukkan nomor kolom acuan (contoh: 2 untuk kolom B)", Type:=1)
    If fieldNumber = 0 Then Exit Sub

    ' Pilih folder output
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Pilih Folder untuk Menyimpan File Output"
        If .Show = -1 Then
            folderPath = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With

    ' Tentukan worksheet sumber
    Set wsSource = ThisWorkbook.Sheets(1)

    ' Cari ukuran data
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

    ' Range data
    Set rngData = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, lastCol))
    Set colKelas = wsSource.Range(wsSource.Cells(2, fieldNumber), wsSource.Cells(lastRow, fieldNumber))

    ' Copy nilai unik
    wsSource.Columns(fieldNumber).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsSource.Range("Z1"), Unique:=True
    Set rngUnique = wsSource.Range("Z2:Z" & wsSource.Cells(wsSource.Rows.Count, "Z").End(xlUp).Row)

    totalUnique = rngUnique.Rows.Count

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    copiedCount = 0
    i = 1

    For Each cell In rngUnique
        kelasName = cell.Value

        ' Update Status
        Application.StatusBar = "Memproses (" & i & "/" & totalUnique & "): " & kelasName

        ' Buat workbook baru
        Set wbNew = Workbooks.Add
        Set wsNew = wbNew.Sheets(1)

        ' Copy header
        rngData.Rows(1).Copy Destination:=wsNew.Range("A1")

        ' Filter dan copy data
        rngData.AutoFilter Field:=fieldNumber, Criteria1:=kelasName
        rngData.SpecialCells(xlCellTypeVisible).Copy Destination:=wsNew.Range("A2")

        ' Hapus filter
        wsSource.AutoFilterMode = False

        ' Simpan file
        wbNew.SaveAs Filename:=folderPath & "Kelas_" & kelasName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        wbNew.Close SaveChanges:=False

        i = i + 1
    Next cell

    ' Bersihkan
    wsSource.Range("Z:Z").ClearContents
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    MsgBox "Data berhasil dipisahkan ke folder: " & vbCrLf & folderPath, vbInformation
End Sub

Tips Penting Saat Menggunakan VBA Split Data

• Data harus rapi: Tidak ada baris kosong di tengah.
• Gunakan nama file aman: Hindari karakter ilegal dalam nama file.
• Backup file utama: Selalu simpan salinan sebelum menjalankan macro.
• Gunakan format .xlsm: Agar macro bisa disimpan dan dijalankan dengan baik.

Penutup

Dengan VBA, memisahkan data dalam Excel menjadi beberapa file terpisah berdasarkan kategori seperti Kelas, Cabang, atau Divisi menjadi sangat mudah dan cepat. Dibandingkan melakukannya secara manual, script ini menghemat banyak waktu kerja.

Script sederhana cocok untuk pemula, sedangkan script advance ideal untuk pekerjaan yang lebih kompleks dan profesional.

Semoga artikel ini membantu dan menjadi referensi Anda dalam mengolah data besar di Excel!

Jika ada pertanyaan silahkan tulis di kolom komentar