Khi chúng ta xử lý nhiều trang tính Excel, đôi khi chúng ta phải sao chép dữ liệu từ một bảng tính sang bảng tính khác với một số điều kiện nhất định để dễ đọc hơn. Việc triển khai VBA là phương pháp hiệu quả nhất, nhanh nhất và an toàn nhất để chạy bất kỳ thao tác nào trong Excel. Trong bài viết này, chúng tôi sẽ chỉ cho bạn cách sao chép dữ liệu sang trang tính khác với bộ lọc nâng cao trong Excel bằng macro VBA.
Tải Xuống Sổ Làm Việc
Bạn có thể tải xuống miễn phí sổ làm việc Excel thực hành từ đây.
3 Phương Pháp Với VBA Để Sao Chép Dữ Liệu Sang Trang Tính Khác Với Bộ Lọc Nâng Cao Trong Excel
Hãy nhìn vào tập dữ liệu sau. Có một trang tính Excel tên là Gốc (Original). Trang tính này bao gồm một số dữ liệu từ phạm vi B4 đến E12. Phạm vi đó cũng bao gồm các giá trị trùng lặp. Phạm vi G4 đến H5 giữ tiêu chí (criteria). Điều chúng ta muốn làm là, chúng ta sẽ sao chép dữ liệu từ phạm vi B5:E12, khi John từ cột Tên (Name) có Điểm (Marks) nhỏ hơn 80 (tiêu chí trong Ô G4:H5) và dán những dữ liệu cụ thể đó sang các trang tính khác với Bộ Lọc Nâng Cao (Advanced Filter) trong Excel bằng ba phương pháp khác nhau.
Chúng ta sẽ xem cách chèn macro hardcoded để sao chép dữ liệu, cách bộ lọc nâng cao bằng lựa chọn và cách chuyển dữ liệu từ trang tính này sang trang tính khác bằng cách ghi lại macro. Và tập dữ liệu trên sẽ là ví dụ để thực thi tất cả các phương pháp được đề cập.
1. Nhúng Mã VBA Để Sao Chép Dữ Liệu Sang Trang Tính Khác Với Bộ Lọc Nâng Cao Trong Excel
Ở đây, bạn sẽ học mã VBA để chỉ sao chép dữ liệu từ trang tính Gốc (Original) khi Điểm Của John Nhỏ Hơn 80 sang một trang tính tên là Đích (Target) với bộ lọc nâng cao.
Các Bước:
- Đầu tiên, nhấn Alt + F11 trên bàn phím hoặc đi đến tab Phát Triển (Developer) -> Visual Basic để mở Trình Soạn Thảo Visual Basic (Visual Basic Editor).
- Tiếp theo, trong cửa sổ mã bật lên, từ thanh menu, nhấp Chèn (Insert) -> Module.
- Sau đó, sao chép mã sau và dán nó vào cửa sổ mã.
Sub AdvancedFilterCode() Dim iRange As Range Dim iCriteria As Range 'set the range to filter and the criteria range Set iRange = Sheets("Original").Range("B4:E12") Set iCriteria = Sheets("Original").Range("G4:H5") 'copy the filtered data to the destination iRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=iCriteria, CopyToRange:=Sheets("Target").Range("B4:E4"), Unique:=True End SubMã của bạn hiện đã sẵn sàng để chạy.
- Bây giờ, nhấn F5 trên bàn phím hoặc từ thanh menu chọn Chạy (Run) -> Chạy Sub/UserForm. Bạn cũng có thể chỉ nhấp vào biểu tượng Chạy nhỏ trong thanh menu con để chạy macro.
Sau khi thực thi mã, hãy nhìn vào hình ảnh dưới đây để xem kết quả.
Kết quả, chỉ dữ liệu nơi Điểm Của John Nhỏ Hơn 80 được sao chép trong trang tính Đích (Target) từ trang tính Gốc (Original) với Bộ Lọc Nâng Cao của VBA.
Đọc Thêm: Cách Sử Dụng Bộ Lọc Nâng Cao Để Sao Chép Dữ Liệu Sang Trang Tính Khác Trong Excel
2. Triển Khai Macro VBA Để Lọc Dữ Liệu Theo Lựa Chọn Của Người Dùng
Bây giờ, bạn sẽ học cách lọc chỉ dữ liệu từ trang tính Gốc (Original) khi Điểm Của John Nhỏ Hơn 80 sang một trang tính tên Đích Đến (Destination) từ một phạm vi do người dùng chọn với bộ lọc nâng cao của VBA trong Excel.
Các Bước:
- Cùng cách như trước, mở Trình Soạn Thảo Visual Basic từ tab Phát Triển (Developer) và Chèn (Insert) một Module trong cửa sổ mã.
- Sau đó, trong cửa sổ mã, sao chép mã sau và dán nó.
Sub AdvancedFilterBySelection() Dim iTrgt As String Dim iRange As Range Dim iCriteria As Range Dim iDestination As Range On Error Resume Next iTrgt = ActiveWindow.RangeSelection.Address Set iRange = Application.InputBox("Select Range to Filter", "Excel", iTrgt, , , , , 8) If iRange Is Nothing Then Exit Sub Set iCriteria = Application.InputBox("Select Criteria Range", "Excel", "", , , , , 8) If iCriteria Is Nothing Then Exit Sub Set iDestination = Application.InputBox("Select Destination Range", "Excel", "", , , , , 8) If iDestination Is Nothing Then Exit Sub iRange.AdvancedFilter xlFilterCopy, iCriteria, iDestination, False iDestination.Worksheet.Activate iDestination.Worksheet.Columns.AutoFit End SubMã của bạn hiện đã sẵn sàng để chạy.
- Bây giờ, Chạy (Run) macro.
- Sau đó, một hộp thoại sẽ xuất hiện. Chọn phạm vi mà bạn muốn lọc (trong trường hợp của chúng ta, nó là từ phạm vi B4 đến E12).
- Sau đó, nhấn OK.
- Tiếp theo, một hộp thoại khác sẽ xuất hiện. Lần này bạn phải chọn phạm vi tiêu chí mà bạn đã lưu trong tập dữ liệu của mình (phạm vi tiêu chí từ Ô G4 đến H5 cho tập dữ liệu của chúng ta).
- Lại nhấn OK.
- Lần cuối, một hộp thoại khác sẽ xuất hiện. Và bây giờ, bạn phải chọn phạm vi đích nơi bạn muốn lưu dữ liệu đã sao chép. Trong trường hợp của chúng ta, nó là Ô B2 trong trang tính Đích Đến (Destination).
- Sau đó, nhấn OK.
Để xem kết quả, hãy nhìn vào hình ảnh dưới đây.
Cuối cùng, chỉ dữ liệu nơi Điểm Của John Nhỏ Hơn 80 được sao chép trong trang tính Đích Đến (Destination) từ trang tính Gốc (Original) với Bộ Lọc Nâng Cao của VBA.
Nội Dung Liên Quan: Excel Advanced Filter Không Hoạt Động (2 Lý Do & Giải Pháp)
Đọc Tương Tự
- Cách Sử Dụng Bộ Lọc Nâng Cao Nếu Phạm Vi Tiêu Chí Chứa Văn Bản Trong Excel
- Bộ Lọc Nâng Cao Động Excel (VBA & Macro)
- Bộ Lọc Nâng Cao Với Phạm Vi Tiêu Chí Trong Excel (18 Ứng Dụng)
- Bộ Lọc Nâng Cao Với Nhiều Tiêu Chí Trong Excel (15 Ví Dụ Phù Hợp)
- Ví Dụ Excel VBA: Sử Dụng Bộ Lọc Nâng Cao Với Tiêu Chí (6 Tiêu Chí)
3. Áp Dụng Macro VBA Để Sao Chép Dữ Liệu Sang Trang Tính Khác Bằng Cách Ghi Lại Macro Trong Excel
Trong phần này, bạn sẽ biết giải pháp cho cùng vấn đề trích xuất chỉ dữ liệu từ trang tính Gốc (Original) khi Điểm Của John Nhỏ Hơn 80 sang một trang tính tên Đã Lọc (Filtered) bằng Ghi Lại Macro (Macro Recording) của VBA trong Excel.
Các Bước:
- Đầu tiên, mở một trang tính mới (trong trường hợp của chúng ta, là trang tính Đã Lọc (Filtered)).
- Trong trang tính đó, chỉ lưu hàng tiêu đề của tập dữ liệu gốc.
- Tiếp theo, đi đến trang tính Gốc (Original). Bạn sẽ thấy một dấu hiệu macro nhỏ ở góc dưới bên trái của trang tính. Nhấp vào dấu hiệu để bắt đầu ghi lại macro.
- Khi đó, một cửa sổ bật lên Ghi Macro (Record Macro) sẽ xuất hiện. Cung cấp một Tên Macro mà bạn muốn. Chúng ta định nghĩa AdvancedFilter là Tên Macro của chúng ta.
- Tiếp theo, chọn nơi bạn muốn lưu macro. Chúng ta muốn lưu macro trong sổ làm việc hiện có, nên chúng ta chọn Sổ Làm Việc Này (This Workbook).
- Sau đó, nhấp OK.
- Bây giờ, quay lại trang tính Gốc (Original) và bạn sẽ nhận thấy macro bạn vừa khởi tạo đã bắt đầu ghi lại.
- Sau đó, bạn phải đi đến trang tính sẽ chứa dữ liệu đã sao chép (ví dụ trang tính Đã Lọc (Filtered)).
- Có một ô hoạt động (active cell) trong trang tính đó và đi đến Dữ Liệu (Data) -> Nâng Cao (Advanced).
- Tiếp theo, một hộp thoại Bộ Lọc Nâng Cao (Advanced Filter) sẽ xuất hiện.
- Đầu tiên, chọn tùy chọn Sao Chép Sang Vị Trí Khác (Copy to another location) từ phần Hành Động (Action).
- Bây giờ, trong hộp văn bản bên cạnh Phạm Vi Danh Sách (List range), đi đến trang tính Gốc (Original) và chọn phạm vi để lọc (cho tập dữ liệu của chúng ta, phạm vi là B4:E12).
- Sau đó, trong hộp văn bản bên cạnh Phạm Vi Tiêu Chí (Criteria range), chọn phạm vi tiêu chí (Điểm Của John Nhỏ Hơn 80) được lưu trong trang tính Gốc (Original) (cho tập dữ liệu của chúng ta, phạm vi là G4:H5).
- Bây giờ, trong hộp văn bản bên cạnh Sao Chép Đến (Copy to), đi đến trang tính Đã Lọc (Filtered), trang tính nơi bạn muốn lưu dữ liệu đã sao chép và chọn phạm vi tiêu đề (cho tập dữ liệu của chúng ta, phạm vi là B4:E4).
- Cuối cùng, nhấp OK.
Để xem kết quả do toàn bộ quy trình này tạo ra, hãy nhìn vào hình ảnh dưới đây. Chỉ dữ liệu nơi Điểm Của John Nhỏ Hơn 80 được sao chép trong trang tính Đã Lọc (Filtered) từ trang tính Gốc (Original) với ghi lại macro.
- Bây giờ, nhấp vào dấu hiệu macro ở góc dưới bên trái của trang tính để dừng ghi lại macro. Bây giờ bạn đã có một macro đã ghi lại sẽ thực hiện quy trình trên mô tả mỗi khi bạn chạy nó.
Nhưng phương pháp này có một nhược điểm. Nếu bạn thêm dữ liệu mới vào trang tính Gốc (Original), trang tính Đã Lọc (Filtered) sẽ không được cập nhật ngay cả khi dữ liệu thỏa mãn tiêu chí.
Chúng ta muốn trang tính Đã Lọc (Filtered) mới được cập nhật tự động bằng cách thực thi mã khi chúng ta thêm dữ liệu mới vào trang tính Gốc (Original). Để làm điều đó, chúng ta cần sửa đổi mã một chút.
Tất cả các bước để đạt được điều chúng ta muốn được trình bày dưới đây.
Các Bước:
- Đầu tiên, từ tab, chọn Xem (View) -> Macro -> Xem Macro (View Macros).
- Sau đó, một cửa sổ bật lên Macro sẽ xuất hiện. Chọn Tên Macro mà bạn vừa tạo bằng ghi lại (AdvancedFilter cho trường hợp của chúng ta).
- Sau đó, nhấp Chỉnh Sửa (Edit).
- Mã đằng sau macro ghi lại sẽ xuất hiện trong cửa sổ mã (xem hình ảnh dưới đây).
- Bây giờ, xóa phần được đánh dấu màu xanh (được hiển thị trong hình ảnh dưới đây) khỏi mã.
- Sau đó, sửa đổi mã như hình ảnh sau.
- Bây giờ, mã cập nhật sẽ là:
Sub AdvancedFilter() Sheets("Original").Range("B4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Original").Range("G4:H5"), CopyToRange:=Sheets("Filtered").Range("B4:E4"), Unique:=False End Sub- Lưu mã này.
- Bây giờ, quay lại trang tính Gốc (Original) và thêm dữ liệu mới sẽ rơi dưới tiêu chí. Ví dụ, chúng ta thêm một hàng khác của thông tin John với Điểm Lấy Được Là 76 rơi dưới tiêu chí Điểm Nhỏ Hơn 80.
- Sau đó, Chạy (Run) mã và nhìn vào hình ảnh sau cho kết quả.
- Có một hàng mới được sao chép trong trang tính Đã Lọc (Filtered) của thông tin John với Điểm 76 thỏa mãn tiêu chí (Điểm < 80).
Đọc Thêm: Cách Sử Dụng Bộ Lọc Nâng Cao Cho Chỉ Các Bản Ghi Duy Nhất Trong Excel
Kết Luận
Tóm lại, bài viết này đã chỉ cho bạn 3 tiêu chí khác nhau về cách sao chép dữ liệu sang trang tính khác với bộ lọc nâng cao trong Excel với macro VBA. Tôi hy vọng bài viết này đã rất có lợi cho bạn. Thoải mái đặt bất kỳ câu hỏi nào liên quan đến chủ đề.
Các Bài Viết Liên Quan
- Cách Sử Dụng Bộ Lọc Nâng Cao Để Loại Trừ Ô Trống Trong Excel (3 Mẹo Dễ Dàng)
- Excel VBA: Bộ Lọc Nâng Cao Với Nhiều Tiêu Chí Trong Một Phạm Vi (5 Phương Pháp)
- Cách Sử Dụng Bộ Lọc Nâng Cao Cho Chỉ Các Bản Ghi Duy Nhất Trong Excel
- Áp Dụng Bộ Lọc Nâng Cao Để Sao Chép Sang Vị Trí Khác Trong Excel
- Excel Advanced Filter: Áp Dụng "Không Chứa" (2 Phương Pháp)
- Áp Dụng Bộ Lọc Nâng Cao Dựa Trên Nhiều Tiêu Chí Trong Một Cột Trong Excel