Cách hợp nhất các tệp Excel bằng VBA Macro
VBA trong Excel là một phần không thể thiếu của tự động hóa Excel và việc sử dụng cũng như lợi ích của VBA không thể bị xác định. Nếu bạn đang trong một trận chiến khó khăn khi cố gắng hợp nhất nhiều trang tính và sổ làm việc trong Excel, chúng tôi sẵn sàng trợ giúp.
Các macro được đề cập trong hướng dẫn này sẽ giúp bạn đạt được nhiệm vụ dường như không thể vượt qua trong vài giây (hoặc vài phút, nếu nội dung dữ liệu lớn).
Bằng cách làm theo hướng dẫn này, bạn sẽ tạo macro VBA của riêng mình trong Excel và hợp nhất nhiều trang tính thành một tệp duy nhất một cách hiệu quả.
Mục Lục
Hợp nhất nhiều trang tính Excel thành một tệp
Đối với tác vụ này, dữ liệu được lưu trữ trong các trang tính sau:
Tên trang tính được liệt kê ở trên chỉ mang tính chất minh họa. Macro VBA này là chung và không phụ thuộc vào tên trang tính; bạn có thể tùy chỉnh mã để sử dụng nó với bất kỳ (các) tên trang tính nào.
Điều kiện tiên quyết để chạy mã
Có một số điều kiện tiên quyết để chạy mã VBA được liệt kê bên dưới.
Bạn cần lưu trữ mã macro trong một tệp Excel mới. Lưu sổ làm việc này với .xlsm sự mở rộng. Bạn có thể lưu sổ làm việc macro VBA với bất kỳ tên nào.
Mở một tệp Excel mới; nhấn Alt + F11 trên bàn phím của bạn để mở trình soạn thảo Excel VBA. Khi trình chỉnh sửa mở ra, hãy thêm một mô-đun mã mới bằng cách nhấp vào Chèn ở trên cùng. Lựa chọn Mô-đun để chèn một mô-đun mới; đây là nơi bạn sẽ nhập mã macro VBA được cung cấp bên dưới.
Các bảng dữ liệu sẽ được hợp nhất hoàn toàn phải nằm trong một sổ làm việc riêng biệt khác. Tên của sổ làm việc và trang tính có thể là bất kỳ tên nào bạn chọn.
Ngay sau khi bạn thực thi mã VBA, macro VBA sẽ xoay vòng qua từng trang tính có sẵn trong sổ làm việc chính (sổ làm việc dữ liệu) và dán nội dung vào một trang tính mới được thêm vào trong cùng một sổ làm việc.
Dữ liệu tổng hợp sẽ có sẵn trong trang tính có tên Hợp nhất, củng cố.
Chạy mã VBA
Đã đến lúc chạy mã macro mới được lưu. Sao chép-dán mã này vào mô-đun của trình soạn thảo VBA:
Sub consolidate_shts()
'declare the various variables used within the code and the vba data types
Dim sht As Worksheet, sht1 As Worksheet, lastrow As Integer, lastrow1 As Integer
'disable screen flickering and alert pop-ups during the execution
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'store the name of the primary workbook in the a macro variable. Replace Test.xlsx with the name of your primary workbook
Set wbk1 = Workbooks("Test.xlsx")
'activate the workbook before performing the function(s) on it
wbk1.Activate
'run a vba for loop to check if a sheet Consolidated already exists. If it exists, the for loop will delete it.
For Each sht In wbk1.Sheets
If sht.Name = "Consolidated" Then sht.Delete
Next sht
'Add a new sheet to store the newly consolidated data
Worksheets.Add.Name = "Consolidated"
'Add some headers to each individual column within the consolidated sheet
With Sheets("Consolidated")
.Range("a1").Value = "OrderDate"
.Range("b1").Value = "Region"
.Range("c1").Value = "Rep"
.Range("d1").Value = "Item"
.Range("e1").Value = "Units"
.Range("f1").Value = "UnitCost"
.Range("g1").Value = "Total"End With
'The newly created sheet consolidated will hold the consolidated data from each individual sheet in the primary workbook
For i = 1 To wbk1.Worksheets.Count
If Sheets(i).Name <> "Consolidated" Then
'Capture the last populated row from the data sheets in the workbook
lastrow = Sheets(i).Range("a1").End(xlDown).Row
'Capture the last populated row in the Consolidated sheet
lastrow1 = wbk1.Sheets("Consolidated").Range("a1048576").End(xlUp).Row + 1
'Copy data from source sheet and paste it in the consolidated sheet
Sheets(i).Range("a2:g" & lastrow).Copy Destination:=Sheets("Consolidated").Range("a" & lastrow1)
End If
Next i
'Enable Excel VBA functions for future use
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
Giải thích mã VBA
Trước tiên, hãy khai báo tất cả các biến bạn đang sử dụng trong mã và gán chúng với kiểu dữ liệu VBA chính xác để làm cho mã chạy liền mạch.
Khi bạn khai báo các biến, bạn cần thực hiện một số công việc quản lý cơ bản. Điều này được thực hiện bằng cách tắt nhấp nháy màn hình và tắt cảnh báo bật lên. Ví dụ: khi bạn xóa một trang tính hiện có bằng mã VBA, một lời nhắc trong Excel sẽ yêu cầu xác nhận trước khi xóa trang tính. Những lời nhắc như thế này được loại bỏ để tăng cường tốc độ thực thi.
Trong bước tiếp theo, bạn cần xác định tên sổ làm việc chứa tất cả dữ liệu của bạn. Thay thế Test.xlsx với tên và phần mở rộng của tên sổ làm việc của bạn. Đảm bảo rằng bạn đặt tên bằng dấu ngoặc kép.
Kích hoạt sổ làm việc chính và xóa bất kỳ trang tính hiện có nào có tên Hợp nhất, củng cố để loại bỏ mọi dữ liệu đã lưu trữ trước đó. Mã VBA chuyển đổi qua từng trang tính và ngay khi gặp tên trang tính Hợp nhất, củng cố nó sẽ xóa nó. Điều này được thực hiện bằng cách sử dụng câu lệnh VBA IF, câu lệnh này sẽ kiểm tra các điều kiện logic và xóa trang tính ngay sau khi điều kiện được đáp ứng.
Một trang tính mới được thêm vào sổ làm việc chính để lưu trữ dữ liệu tổng hợp. Sau đó, các tiêu đề được định dạng trước, tiêu chuẩn hóa được thêm vào trang tính này. Bạn có thể thay đổi giá trị của tiêu đề (tiêu đề cột) bằng cách cập nhật thông tin bên cạnh tham chiếu ô trong dấu ngoặc kép.
Ví dụ: .Range (“a1”) = “Ngày đặt hàng” có thể được thay thế bằng .Range (“a1”) = “OrderNumber”
Tiếp theo, vòng lặp VBA FOR chuyển đổi qua từng trang tính, sao chép nội dung của trang tính và dán nội dung vào Hợp nhất, củng cố trang tính trước khi chuyển sang trang tính tiếp theo trong sổ làm việc. Quá trình này lặp lại cho đến khi tất cả các trang tính được sao chép hết.
Trong quá trình này, tất cả các hàng được tự động tính toán và dán vào trang tính Hợp nhất. Hàng được điền cuối cùng được tính toán tự động trước khi dữ liệu được dán vào. Macro là động và có thể điều chỉnh theo các hàng dữ liệu khác nhau trong mỗi trang tính.
Sau khi dữ liệu từ tất cả các trang tính được dán vào trang tính hợp nhất chính, macro sẽ di chuyển đến chân cuối cùng của mã. Các chức năng VBA bị vô hiệu hóa ban đầu được bật lại để sử dụng trong tương lai.
Hợp nhất nhiều trang tính bằng Excel VBA Macro
Excel VBA là một ngôn ngữ lập trình không cần thiết, hoạt động tốt với tất cả các thành phần của Excel. Mỗi đoạn mã đều rất cần thiết và điều quan trọng cần nhớ là việc thực thi phụ thuộc vào hệ thống thực thi từng dòng, vì vậy bạn không nên thay đổi thứ tự của các dòng mã.
Để tùy chỉnh mã cho các yêu cầu của bạn, bạn có thể thực hiện các thay đổi cần thiết và chạy mã này để hợp nhất dữ liệu một cách hiệu quả và hiệu quả trong vài giây.
Đọc tiếp
Giới thiệu về tác giả
