Hướng dẫn cách tách sheet trong Excel thành các file riêng bằng VBA

Nếu file Excel của bạn đang gặp tình trạng quá tải do chứa quá nhiều sheet, hãy nghĩ đến giải pháp tách sheet trong Excel thành các file riêng lẻ. Trong bài học Excel ngày hôm nay, Gitiho sẽ hướng dẫn bạn cách thực hiện thao tác này một cách đơn giản và nhanh chóng bằng lệnh VBA Excel, Cùng theo dõi nhé!

Xem thêm: Hướng dẫn cách gộp các sheet trong Excel kèm theo code VBA

Hướng dẫn cách tách sheet trong Excel thành file riêng lẻ

Nếu bạn đang làm việc với một file Excel tổng hợp dữ liệu mà muốn tách nội dung các sheet ra làm các file riêng biệt thì phải làm thế nào? Đúng là Excel không được cài đặt sẵn tính năng tạo các file nhỏ từ một file Excel lớn, nhưng sự thật là bạn vẫn có thể thực hiện cách tách sheet trong Excel một cách thủ công. Đây là cách chúng ta vẫn hay áp dụng.

Tuy nhiên, khi phải lần lượt tách các nội dung trong một file lớn thành các file nhỏ lẻ, bạn sẽ thấy thao tác này cực kỳ tốn thời gian và dễ xảy ra sai sót đáng tiếc. Thay vào đó, hãy để mình chỉ cho bạn cách sử dụng các lệnh VBA Excel để tách sheet trong Excel một cách hiệu quả nhất nhé.

Cách tách sheet thành file trong Excel dưới đây chắc chắn sẽ giúp bạn vừa tiết kiệm thời gian, vừa loại bỏ các sai sót của thao tác thủ công. Vậy thì cùng tìm hiểu ngay thôi!

Cách tách sheet trong Excel thành file Excel riêng lẻ

VBA là một ngôn ngữ lập trình được tích hợp trong Excel giúp bạn có thể thêm các các chức năng tùy chỉnh hoặc tự động hóa thao tác với dữ liệu chẳng hạn như lấy dữ liệu từ sheet này sang sheet khác, tách sheet trong Excel, và hơn thế nữa.

Để giúp bạn hiểu rõ hơn về phương pháp tách sheet trong Excel bằng lệnh VBA Excel, chúng ta sẽ thực hành các bước quá một ví dụ nhé. Giả sử mình có một file tổng hợp dữ liệu, trong đó dữ liệu mỗi tháng được trình bày trong một sheet riêng như hình dưới đây:

Bây giờ mình cần sử dụng dữ liệu một tháng cụ thể trong các sheet này để làm báo cáo.

Vấn đề xảy ra khi làm việc trên file tổng hợp như thế này chính là nguy cơ Excel ngừng hoạt động bất cứ lúc nào. Do khối lượng dữ liệu ngày càng trở nên khổng lồ, Excel sẽ bắt đầu xuất hiện các dấu hiệu tốc độ và hiệu năng hoạt động kém ổn định. Khi nhận ra các dấu hiệu này, bạn hãy tìm đến giải pháp tách sheet trong Excel ngay nhé.

Trước khi thực hành cách tách sheet thành file trong Excel với lệnh VBA Excel, có một vài thao tác bạn cần thực hiện như sau:

• Bước 1: Tạo một folder trên thiết bị để lưu tất cả các file Excel sau khi được tách riêng lẻ.
• Bước 2: Lưu file Excel hiện tại (file tổng hợp chứa tất cả các sheet muốn tách lẻ) trong folder vừa tạo.

Khi đã hoàn thành các thao tác trên, bạn có thể bắt đầu áp dụng cách tách sheet trong Excel rồi. Dưới đây là lệnh VBA Excel bạn cần:

'Tach sheet trong Excel thanh file rieng le
Sub TachfileExcel()
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 Sub

Lệnh VBA Excel này sẽ xác định vị trí folder bạn đã sử dụng để lưu file Excel hiện tại. Do đó, trước khi chạy lệnh, việc quan trọng nhất cần làm là lưu file của bạn trong folder.

Nếu bạn muốn hiểu rõ hơn về cách thức hoạt động của lệnh VBA Excel phía trên và cách nó giúp bạn tách sheet trong Excel, mình sẽ giải thích kỹ hơn dưới đây:

Lệnh VBA Excel này sử dụng một vòng lặp For Next đơn giản để đi qua lần lượt các sheet tồn tại trong file hiện tại, sau đó tạo ra một bản sao của các sheet này và lưu chúng lại trong cùng folder chứa file gốc (file hiện tại).

Xem thêm: Cách sử dụng vòng lặp For.. Next trong VBA

Để áp dụng lệnh VBA trên, bạn hãy thực hiện các bước dưới đây:

Bước 1: Đi đến thẻ Developer > nhóm Code > Visual Basic.

Bước 2: Cửa sổ Microsoft Visual Basic for Applications hiện ra. Nhấn chuột phải tại một trong các sheet Excel > Insert > Module.

Bước 3: Tại module mới được tạo, copy đoạn mã VBA Excel phía trên.

Bước 4: Nhấn chuột vào một dòng bất kỳ trong đoạn code VBA Excel đã copy và nhấn biểu tượng nút play màu xanh lá trên thanh công cụ.

Bước 5: Hộp thoại Macros hiện ra. Chọn macro TachfileExcel (trong trường hợp bạn có nhiều hơn một macro đang chạy trên Excel) và nhấn Run.

Giờ thì đợi một chút để Excel chạy macro và dưới đây là kết quả bạn sẽ nhận được khi mở folder lưu file.

Như bạn thấy, toàn bộ các sheet trong file tổng hợp đã được tách lẻ ra thành các file Excel riêng biệt và được lưu dưới tên file chính là tên sheet. Vậy là chỉ trong vài giây, bạn đã có thể tách sheet trong Excel một cách tự động mà không phải tốn thời gian cho các thao tác thủ công rườm rà.

Vì bạn đã sử dụng lệnh VBA Excel trong File tổng hợp nên hãy nhớ lưu lại file này bằng đuôi .xlsx hoặc .xlsm (định dạng cho phép chạy macro). Bằng cách lưu trữ file dưới các định dạng này, bạn mới có thể đảm bảo toàn bộ macro được lưu lại và hoạt động bình thường mỗi lần mở file.

Xem thêm: Hướng dẫn thiết lập phân quyền cho phép bật macro trong Excel

Cách tách sheet trong Excel thành file PDF riêng lẻ

Nếu như bạn muốn tách sheet trong Excel và lưu dưới định dạng các file PDF riêng lẻ thay vì file Excel như ở phần trên, lệnh VBA Excel hoàn toàn có thể giúp bạn làm được điều đó. Tất cả các bước đều tương tự như những gì chúng ta đã thực hành.

Trước khi thực hiện chạy lệnh VBA Excel, hãy đảm bảo mình đã thực hiện các thao tác sau. Đây là bước chuẩn bị vô cùng quan trọng, do đó đừng quên nhé.

Bước 1: Tạo một folder trên thiết bị để lưu tất cả các file Excel sau khi được tách riêng lẻ.

Bước 2: Lưu file Excel hiện tại (file tổng hợp chứa tất cả các sheet muốn tách lẻ) trong folder vừa tạo.

Lệnh VBA Excel dưới đây sẽ giúp bạn tách sheet trong Excel thành các file PDF riêng lẻ.

'Tach sheet trong Excel thanh file rieng le
Sub TachfilePDF()
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

Tương tự lệnh VBA Excel chúng ta đã tìm hiểu với cách tách sheet thành file trong Excel ở phần trên, lệnh VBA Excel tách sheet thành file PDF trong phần này hoạt động như sau:

Lệnh xác định vị trí folder lưu trữ file Excel hiện tại (file gốc). Sau đó, cấu trúc vòng lặp For Next đi qua lần lượt các sheet tồn tại trong file hiện tại, tạo ra một bản PDF của các sheet này và lưu chúng lại trong cùng folder chứa file gốc.

Để áp dụng cách tách sheet trong Excel thành các file PDF bằng lệnh trên, bạn hãy thực hiện các bước dưới đây:

Bước 1: Đi đến thẻ Developer > nhóm Code > Visual Basic.

Bước 2: Cửa sổ Microsoft Visual Basic for Applications hiện ra. Nhấn chuột phải tại một trong các sheet Excel > Insert > Module.

Bước 3: Tại module mới được tạo, copy đoạn mã VBA Excel phía trên.

Bước 4: Nhấn chuột vào một dòng bất kỳ trong đoạn code VBA Excel đã copy và nhấn biểu tượng nút play màu xanh lá trên thanh công cụ.

Bước 5: Hộp thoại Macros hiện ra. Chọn macro TachfilePDF (trong trường hợp bạn có nhiều hơn một macro đang chạy trên Excel) và nhấn Run.

Các thao tác này hoàn toàn giống như những gì chúng ta đã thực hành ở phần trên. Nếu như các bạn chưa nắm chắc thao tác, hãy kéo lại lên trên để xem hướng dẫn với hình ảnh nhé.

Xem thêm: Hướng dẫn cách tự tạo hàm UDF trong Excel bằng lệnh VBA

Cách tách riêng các sheet chứa chuỗi ký tự nhất định trong tên thành các file Excel riêng lẻ

Trong trường hợp bạn cần lọc ra các tên sheet chứa chuỗi ký tự nhất định và tách nhỏ chúng ra thành các file Excel riêng biệt, VBA Excel vẫn là giải pháp hàng đầu bạn nên nghĩ đến. Khi đọc đến phần này, có lẽ bạn sẽ phải công nhận sự lợi hại của tính năng VBA đúng không nào?

Giả sử bạn có một file Excel tổng hợp dữ liệu trong nhiều năm và các sheet được đặt tên theo cú pháp năm đi kèm với nội dung của sheet. Bây giờ bạn cần tách riêng các sheet trình bày dữ liệu cho năm 2020 và lưu chúng thành các file Excel riêng lẻ.

Vẫn như các phần hướng dẫn phía trên, đừng quên khâu chuẩn bị trước khi áp dụng cách tách sheet thành file trong Excel:

Bước 1: Tạo một folder trên thiết bị để lưu tất cả các file Excel sau khi được tách riêng lẻ.

Bước 2: Lưu file Excel hiện tại (file tổng hợp chứa tất cả các sheet muốn tách lẻ) trong folder vừa tạo.

Để tách sheet, trước tiên bạn sẽ cần kiểm tra tên của các sheet. Sau đó chọn các sheet chứa các ký tự "2020" và thực hiện tách sheet trong Excel. Tất nhiên, bạn không cần tự mình làm. Thay vào đó, lệnh VBA Excel dưới đây sẽ giúp bạn thực hiện toàn bộ các bước này:

'Tach sheet trong Excel thanh file rieng le
Sub Tachfile2020()
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 Sub

Trong lệnh VBA này, mình đã sử dụng biến TexttoFind và gán cho nó giá trị "2020" tương ứng với chuỗi ký tự cần xác định trong tên các sheet muốn lọc. Bạn có thể thay đổi giá trị này bằng chuỗi ký tự mình cần tìm. Hãy nhớ đặt nó trong cặp dấu nháy kép "" nhé.

Sau đó, cấu trúc vòng lặp For Next trong lệnh VBA Excel sẽ đi qua tất cả các sheet Excel. Cùng lúc đó, hàm INSTR kiểm tra liệu tên của từng sheet có chứa ký tự "2020" hay không. Nếu kết quả là có, hàm sẽ trả về một con số thứ tự đại diện cho vị trí hàm tìm thấy giá trị "2020". Ngược lại, nếu hàm không tìm thấy "2020" trong tên sheet, hàm sẽ trả về con số 0.

Cách thức hoạt động của lệnh VBA dựa trên cơ sở điều kiện If Then. Nói một cách đơn giản, đây là điều kiện Nếu...thì. Nếu tên sheet chứa giá trị "2020" thì nó sẽ được lưu trữ thành một file Excel riêng biệt. Nếu tên sheet không chứa giá trị "2020" thì không có gì xảy ra.

Xem thêm: Cách viết cấu trúc IF THEN ELSE trong VBA Excel

Bạn đã hiểu về cách thức hoạt động của lệnh VBA này rồi đúng không nào? Giờ thì chúng ta sẽ áp dụng cách tách sheet trong Excel thành các file riêng biệt bằng các thao tác như sau:

Bước 1: Đi đến thẻ Developer > nhóm Code > Visual Basic.

Bước 2: Cửa sổ Microsoft Visual Basic for Applications hiện ra. Nhấn chuột phải tại một trong các sheet Excel > Insert > Module.

Bước 3: Tại module mới được tạo, copy đoạn mã VBA Excel phía trên.

Bước 4: Nhấn chuột vào một dòng bất kỳ trong đoạn code VBA Excel đã copy và nhấn biểu tượng nút play màu xanh lá trên thanh công cụ.

Bước 5: Hộp thoại Macros hiện ra. Chọn macro Tachfile2020 (trong trường hợp bạn có nhiều hơn một macro đang chạy trên Excel) và nhấn Run.

Sau khi thực hiện lần lượt các bước trên, bạn sẽ thấy các sheet chứa giá trị "2020" trong tên đã được tách thành các file riêng trong folder của mình. Tất nhiên, bạn hoàn toàn có thể áp dụng cách tách sheet thành file trong Excel này với các giá trị khác tùy vào yêu cầu cụ thể.

Tổng kết

Hy vọng bài viết đã giúp các bạn thành công tách sheet trong Excel theo đúng yêu cầu của mình. Để tìm hiểu thêm các công dụng thần kỳ của VBA Excel, bạn hãy tham khảo thêm các bài viết về chủ đề này trên blog tin học văn phòng của Gitiho nhé. Mình chắc chắn rằng việc tìm hiểu về VBA sẽ nâng tầm trải nghiệm làm việc trên Excel của bạn đấy.

Đồng thời, bạn có thể nâng cao kiến thức VBA của mình bằng các khóa học sau tại Gitiho:

Gitiho xin cảm ơn bạn đọc và chúc bạn thành công!