Tách trang tính Excel thành nhiều tệp dựa trên cột bằng VBA
Bạn có một dữ liệu lớn trên trang tính excel và bạn cần phân phối trang tính đó thành nhiều trang tính, dựa trên một số dữ liệu trong một cột? Nhiệm vụ rất cơ bản này nhưng tốn thời gian.
Ví dụ, tôi có dữ liệu này. Dữ liệu này có một cột có tên Ngày, Người viết và Tiêu đề. Cột Writer có tên của nhà văn của chức danh tương ứng. Tôi muốn lấy dữ liệu của từng người viết trong các trang tính riêng biệt.
Để làm điều này theo cách thủ công, tôi phải làm như sau:
-
Lọc một tên. Sao chép dữ liệu đã lọc. Thêm một trang tính. Dán dữ liệu. Đổi tên trang tính. Lặp lại tất cả 5 bước trên cho mỗi bước.
Trong ví dụ này, tôi chỉ có ba tên. Hãy tưởng tượng nếu có 100 cái tên.
Bạn sẽ chia dữ liệu thành các trang tính khác nhau như thế nào? Nó sẽ mất rất nhiều thời gian và nó sẽ làm bạn kiệt sức.
Để Tự động hóa quá trình chia trang tính thành nhiều trang tính ở trên, hãy làm theo các bước sau.
Nhấn Alt + F11. Thao tác này sẽ mở VB Editor cho Excel Thêm một mô-đun mới * Sao chép mã bên dưới trong mô-đun.
Sub SplitIntoSheets() With Application .ScreenUpdating = False .DisplayAlerts = False End With ThisWorkbook.Activate Sheet1.Activate 'clearing filter if any On Error Resume Next Sheet1.ShowAllData On Error GoTo 0 Dim lsrClm As Long Dim lstRow As Long 'counting last used row lstRow = Cells(Rows.Count, 1).End(xlUp).Row Dim uniques As Range Dim clm As String, clmNo As Long On Error GoTo handler clm = Application.InputBox("From which column you want create files" & vbCrLf & "E.g. A,B,C,AB,ZA etc.") clmNo = Range(clm & "1").Column Set uniques = Range(clm & "2:" & clm & lstRow) 'Calling Remove Duplicates to Get Unique Names Set uniques = RemoveDuplicates(uniques) Call CreateSheets(uniques, clmNo) With Application .ScreenUpdating = True .DisplayAlerts = True .AlertBeforeOverwriting = True .Calculation = xlCalculationAutomatic End With Sheet1.Activate MsgBox "Well Done!" Exit Sub Data.ShowAllData handler: With Application .ScreenUpdating = True .DisplayAlerts = True .AlertBeforeOverwriting = True .Calculation = xlCalculationAutomatic End With End Sub Function RemoveDuplicates(uniques As Range) As Range ThisWorkbook.Activate Sheets.Add On Error Resume Next ActiveSheet.Name = "uniques" Sheets("uniques").Activate On Error GoTo 0 uniques.Copy Cells(2, 1).Activate ActiveCell.PasteSpecial xlPasteValues Range("A1").Value = "uniques" Dim lstRow As Long lstRow = Cells(Rows.Count, 1).End(xlUp).Row Range("A2:A" & lstRow).Select ActiveSheet.Range(Selection.Address).RemoveDuplicates Columns:=1, Header:=xlNo lstRow = Cells(Rows.Count, 1).End(xlUp).Row Set RemoveDuplicates = Range("A2:A" & lstRow) End Function Sub CreateSheets(uniques As Range, clmNo As Long) Dim lstClm As Long Dim lstRow As Long For Each unique In uniques Sheet1.Activate lstRow = Cells(Rows.Count, 1).End(xlUp).Row lstClm = Cells(1, Columns.Count).End(xlToLeft).Column Dim dataSet As Range Set dataSet = Range(Cells(1, 1), Cells(lstRow, lstClm)) dataSet.AutoFilter field:=clmNo, Criteria1:=unique.Value lstRow = Cells(Rows.Count, 1).End(xlUp).Row lstClm = Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print lstRow; lstClm Set dataSet = Range(Cells(1, 1), Cells(lstRow, lstClm)) dataSet.Copy Sheets.Add ActiveSheet.Name = unique.Value2 ActiveCell.PasteSpecial xlPasteAll Next unique End Sub
Khi bạn chạy thủ tục SplitIntoSheets (), trang tính sẽ được chia thành nhiều trang tính, dựa trên cột nhất định. Bạn có thể thêm nút trên trang tính và gán macro này cho nó.
Cách thức hoạt động
Đoạn mã trên có hai thủ tục và một hàm. Hai thủ tục là SplitIntoSheets (), CreateSheets (uniques As Range, clmNo As Long) và một hàm là RemoveDuplicates (uniques As Range) As Range. Thủ tục đầu tiên là SplitIntoSheets (). Đây là thủ tục chính. Thủ tục này đặt các biến và RemoveDuplicates để lấy các tên duy nhất từ cột nhất định và sau đó chuyển các tên đó đến CreateSheets để tạo trang tính.
RemoveDuplicates nhận một đối số là dải ô chứa tên.
Loại bỏ các bản sao khỏi chúng và trả về một đối tượng phạm vi có chứa các tên duy nhất.
Bây giờ CreateSheets được gọi. Nó cần hai đối số. Đầu tiên là các tên duy nhất và thứ hai là cột không. từ đó chúng tôi nó sẽ phù hợp với dữ liệu. Bây giờ CreateSheets lấy từng tên từ các đơn vị và lọc số cột đã cho theo từng tên. Sao chép dữ liệu đã lọc, thêm trang tính và dán dữ liệu vào đó. Và dữ liệu của bạn được chia thành các trang tính khác nhau trong vài giây.
Bạn có thể tải xuống tệp tại đây.
Chia thành trang tính
Cách sử dụng tệp:
-
Sao chép dữ liệu của bạn trên Sheet1. Đảm bảo rằng nó bắt đầu từ A1.
Nhấp vào Nút Chia thành Trang tính * Nhập ký tự cột mà bạn muốn tách. Nhấp vào Ok.
-
Bạn sẽ thấy một lời nhắc như thế này. Trang tính của bạn được chia nhỏ.
Tôi hy vọng bài viết về tách dữ liệu thành các trang tính riêng biệt hữu ích cho bạn. Nếu bạn có bất kỳ nghi ngờ nào về điều này hoặc về bất kỳ tính năng nào khác của excel, hãy hỏi nó trong phần bình luận bên dưới.