Kode vba untuk membagi file excel menjadi beberapa file berdasarkan kolom

Power Query bukanlah alat yang tepat untuk ini karena tidak menghasilkan file Excel yang berbeda. Anda harus melakukan pekerjaan manual dan menyimpan semua file satu per satu setelah menerapkan berbagai filter di PQ

Alat yang tepat untuk ini adalah Excel itu sendiri jika Anda menginginkan versi otomatis.  

Saya telah menulis makro VBA di mana Anda perlu memilih kolom dan itu akan menghasilkan banyak file yang sesuai dengan entri yang berbeda. Karenanya, jika Anda menjalankan makro setelah memilih kolom Subjek atau hanya memilih sel mana saja di kolom Subjek, Anda akan mendapatkan dua file

Pasal 48 – Membagi Buku Kerja menjadi Beberapa Buku Kerja berdasarkan Kolom

Jika Anda melakukan beberapa transformasi di PQ, lakukan dan simpan hasilnya di Excel. Kemudian jalankan makro di atas

Tutorial ini menjelaskan cara membagi lembar Excel menjadi beberapa file berdasarkan kolom. Di sini saya akan berbicara tentang dua metode berbeda untuk membagi lembar Excel menjadi lembar yang berbeda. Metode ini pada dasarnya menggunakan kolom tertentu dan kemudian mengelompokkan nilai unik. Setelah itu, mereka mengekstrak data untuk nilai-nilai tersebut dan kemudian meletakkannya di lembar yang berbeda. Nanti Anda dapat menyimpan lembar tersebut sebagai file Excel terpisah. Dalam tutorial berikut, saya akan melakukan hal yang sama menggunakan plugin Excel dan melalui kode VBA

Jika Anda memiliki satu file Excel yang berisi data berulang tentang suatu entitas, maka Anda dapat membaginya. Misalnya, jika Anda memiliki catatan tentang klien yang berbeda di lembar Anda dengan banyak catatan, Anda dapat memisahkannya di lembar terpisah. Ini akan menghasilkan data yang terorganisir dan Anda akan memiliki data tentang klien yang berbeda di lembar yang berbeda. Yang harus Anda lakukan hanyalah menekan beberapa klik untuk mengeksekusi kode VBA

Bagaimana Membagi Lembar Excel Menjadi Beberapa File Berdasarkan Kolom?

Metode 1. Pisahkan Lembar Excel menjadi Beberapa File Berdasarkan Kolom menggunakan Kode VBA

Jika Anda tahu cara mengeksekusi kode VBA di dalam Excel, maka ini adalah salah satu metode terbaik untuk membagi lembar Excel dengan mudah. Di sini saya akan mencantumkan dua cuplikan VBA yang dapat Anda jalankan di lembar Excel target. Cuplikan pertama mengekstrak data dari kolom yang ditentukan dan akan menempatkan data di lembar yang berbeda. Cuplikan kedua akan menyimpan lembaran tersebut sebagai file terpisah di direktori yang sama di mana File Excel sumber berada

Berikut adalah langkah-langkah menggunakan kode VBA untuk membagi lembar Excel berdasarkan kolom

Langkah 1. Buka Excel lalu muat lembar di dalamnya yang ingin Anda bagi. Setelah itu, buka editor VBA dengan menekan kombinasi tombol Alt + F11

Langkah 2. Di editor VBA, Anda dapat mengklik kanan di panel kiri untuk memasukkan modul baru. Lihat tangkapan layar di bawah ini. Dan kemudian di modul baru, Anda dapat menempelkan kode ini

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:C1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

Sumber

Langkah 3. Sekarang, jalankan kode ini dengan menekan tombol F5. Atau, Anda dapat mengeklik tombol putar dari bilah alat untuk melakukan hal yang sama. Saat Anda melakukannya, Anda akan melihat bahwa itu akan membuat lembar berbeda dengan nilai unik berbeda dari kolom

Langkah 4. Sekarang, pada titik ini, kami masih menyimpan lembaran di File Excel. Untuk mengekspornya sebagai file Excel terpisah, Anda harus menjalankan kode VBA lainnya. Hapus semua kode dari jendela saat ini dan kemudian rekatkan kode berikut. Jalankan dan kemudian akan membuat file terpisah di folder yang sama di mana file sumber Excel berada

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 & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub_

Dengan cara ini, Anda dapat membagi lembar Excel menjadi beberapa lembar berdasarkan kolom. Yang harus Anda lakukan adalah menjalankan dua cuplikan kode yang berbeda dan menyelesaikan pekerjaan di ujung jari Anda

Metode 2. Pisahkan Lembar Excel menjadi Beberapa File Berdasarkan Kolom menggunakan Plugin Excel

Data XL adalah tambahan Excel gratis yang dapat Anda gunakan untuk membagi lembar Excel menjadi beberapa file berdasarkan kolom. Anda cukup menginstalnya dan kemudian menggunakannya untuk membagi lembar Excel apa pun. Ini bekerja dengan cara yang persis sama seperti kode di atas. Anda dapat menginstalnya melalui file XALM dan kemudian mulai menggunakannya untuk membagi lembaran berdasarkan kolom. Ini memberi Anda GUI sederhana untuk menangani operasi pemisahan lembar. Hanya dibutuhkan rentang yang ingin Anda pisahkan beserta kolom yang akan digunakan untuk membagi lembar. Setelah itu, hanya dengan beberapa klik Anda harus menekan dan Anda akan memiliki banyak file Excel di direktori yang sama

Berikut adalah langkah-langkah menggunakan plugin ini untuk membagi lembar Excel menjadi beberapa file berdasarkan kolom

Langkah 1. Unduh plugin Data XL untuk Excel dari tautan di atas dan kemudian Anda dapat membukanya hanya dengan mengklik dua kali

Langkah 2. Buka lembar Excel yang ingin Anda pisahkan melalui plugin ini. Setelah itu, Anda dapat mengklik "Split Data Tool" dari tab "DATA XL" pada pita Excel. Setelah itu isikan detail seperti range data, tentukan kolom yang akan digunakan untuk membagi data, dll

Langkah 3. Terakhir, tekan tombol “Split Now” untuk membagi lembaran sesuai dengan kolom yang telah Anda tentukan. Ini akan segera membagi lembar dan akan membuat lembar kerja terpisah ke dalam tab Excel yang berbeda. Lihat tangkapan layar ini

Meskipun ada opsi untuk secara langsung menentukan folder tempat Anda ingin menyimpan data terpisah sebagai file Excel terpisah. Tetapi opsi ini sepertinya tidak berfungsi di Windows 10. Jadi, setelah Anda membagi data di lembar yang berbeda, Anda dapat menyimpannya ke file Excel yang terpisah menggunakan kode VBA yang telah saya sebutkan pada metode di atas.

Pikiran terakhir

Metode yang telah saya jelaskan di atas dapat dengan mudah membagi lembar Excel berdasarkan kolom. Itu tergantung pada Anda metode mana yang ingin Anda gunakan untuk membagi Excel. Kedua metode bekerja dengan baik dan dapat dengan mudah membagi lembar Excel hanya dalam beberapa detik. Jadi, jika Anda sedang mencari cara untuk membagi lembar Excel menurut salah satu kolomnya, maka tutorial ini akan sangat membantu

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

Bagaimana cara membagi data menjadi beberapa file Excel berdasarkan kolom di Excel?

Bagaimana cara membagi file Excel menjadi beberapa file di VBA?

Bagaimana cara membagi satu lembar Excel menjadi beberapa file menggunakan makro berdasarkan kolom?