VBA để sao chép dữ liệu sang trang tính khác với bộ lọc nâng cao trong Excel

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ừ bảng tính này sang bảng tính khác với các điều kiện nhất định để dễ đọc hơn. 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ẽ hướng dẫn bạn cách sao chép dữ liệu sang một trang tính khác bằng bộ lọc nâng cao trong Excel với macro VBA.

Tải xuống Workbook

Bạn có thể tải xuống sổ làm việc Excel thực hành miễn phí 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

Nhìn vào tập dữ liệu sau. Có một trang tính Excel có tên là Original . Trang tính này bao gồm một số dữ liệu từ dải ô B4 đến E12 . Phạm vi đó cũng bao gồm các giá trị trùng lặp. Dải G4 đến H5 nắm giữ các tiêu chí . Những gì chúng tôi muốn làm là, chúng tôi sẽ sao chép dữ liệu từ dải ô B5:E12 , khi John từ Tên cột có Dấu hiệu dưới 80 (tiêu chí trong Ô G4:H5 ) và dán các dữ liệu cụ thể đó vào các trang tính khác bằng Bộ lọc nâng cao trong Excel với ba phương pháp khác nhau.

Chúng ta sẽ xem cách chèn macro mã cứng để sao chép dữ liệu , cách nâng cao bộ lọc theo 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 thức đượ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

Tại đây, bạn sẽ học VBA mã để sao chép dữ liệu duy nhất từ ​​ Bản gốc trang tính khi John’s Marks nhỏ hơn 80 sang một trang tính khác có tên Mục tiêu với bộ lọc nâng cao.

Các bước:

• Lúc đầu, nhấn Alt + F11 trên bàn phím của bạn hoặc chuyển đến tab Nhà phát triển -> Visual Basic để mở Visual Basic Editor .

• Tiếp theo, trong cửa sổ mã bật lên, từ thanh trình đơn, nhấp vào Chèn -> Mô-đun .

• Sau đó, sao chép mã sau và dá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 Sub

Mã của bạn hiện đã sẵn sàng để chạy.

• Bây giờ, nhấn F5 trên bàn phím của bạn hoặc từ thanh trình đơn, chọn Run -> Run Sub / UserForm . Bạn cũng có thể chỉ cần nhấp vào biểu tượng Run nhỏ trong thanh menu phụ để chạy macro.

Sau khi thực thi mã, hãy nhìn vào hình ảnh bên dưới để xem kết quả.

Do đó, chỉ dữ liệu trong đó John’s Marks nhỏ hơn 80 được sao chép trong Mục tiêu trang tính từ Bản gốc tờ 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 một trang tính khác trong Excel

2. Triển khai Macro VBA để lọc dữ liệu theo lựa chọn do người dùng xác định

Bây giờ, bạn sẽ học cách lọc dữ liệu duy nhất từ ​​ Bản gốc tờ khi John’s Marks nhỏ hơn 80 đến một trang tính khác có tên Đích 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:

• Tương tự như trước đây, mở Visual Basic Editor từ Nhà phát triển và Chèn a Mô-đun trong cửa sổ mã.
• Sau đó, trong cửa sổ mã, hãy sao chép mã sau và dá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 Sub

Mã của bạn hiện đã sẵn sàng để chạy.

• Bây giờ, Chạy macro.
• Sau đó, một hộp bật lên 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 tôi, nó nằm trong khoảng B4 đến E12 ).
• Sau đó, nhấn OK .

• Tiếp theo, một hộp bật lên 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 trữ trong tập dữ liệu của mình (tiêu chí nằm trong khoảng từ Ô G4 đến H5 cho tập dữ liệu của chúng tôi).
• Một lần nữa, nhấn OK .

• Lần cuối cùng, một hộp bật lên 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 trữ dữ liệu đã sao chép . Trong trường hợp của chúng tôi, đó là Ô B2 ở Điểm đến tờ .
• Sau đó, nhấn OK .

Để xem kết quả, hãy xem hình ảnh bên dưới.

Cuối cùng, chỉ dữ liệu trong đó John’s Marks nhỏ hơn 80 được sao chép ở Điểm đến tờ từ Bản gốc tờ với Bộ lọc nâng cao của VBA .

Nội dung có Liên quan: Bộ lọc nâng cao của Excel không hoạt động (2 lý do và giải pháp)

Bài đọ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ụ Thích hợp)
• Ví dụ VBA trong Excel: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 tính năng ghi macro trong Excel

Trong phần này, bạn sẽ biết giải pháp cho cùng một vấn đề về việc chỉ trích xuất dữ liệu từ Bản gốc trang tính khi John’s Marks nhỏ hơn 80 sang một trang tính khác có tên Đã lọc bằng cách Ghi macro của VBA trong Excel.

Các bước:

• Trước hết, hãy mở một trang tính mới (trong trường hợp của chúng tôi, đó là trang tính Đã lọc ).
• Trong trang tính đó, chỉ lưu trữ hàng tiêu đề của tập dữ liệu gốc.

• Tiếp theo, chuyển đến Bản gốc tờ . Bạn sẽ thấy một dấu hiệu macro nhỏ ở phía dưới cùng bên trái của trang tính. Nhấp vào ký hiệu để bắt đầu ghi macro.

• Sau đó, một Ghi Macro cửa sổ bật lên sẽ xuất hiện. Cung cấp Tên macro mà bạn muốn. Chúng tôi xác định AdvancedFilter là tên macro của chúng tôi .
• Tiếp theo, chọn nơi bạn muốn lưu trữ macro . Chúng tôi muốn lưu trữ macro trong sổ làm việc hiện có, vì vậy chúng tôi đã chọn Sổ làm việc này .
• Sau đó, nhấp vào OK .

• Bây giờ, quay lại Bản gốc tờ và bạn sẽ nhận thấy rằng macro mà bạn vừa khởi tạo đã bắt đầu ghi.

• Sau đó, bạn phải chuyển đến trang tính điều đó sẽ giữ dữ liệu đã sao chép (ví dụ: Đã lọc trang tính).
• Có một ô đang hoạt động trong trang tính đó và chuyển đến Dữ liệu -> Nâng cao .

• Tiếp theo, một Bộ lọc Nâng cao hộp bật lên sẽ xuất hiện.
• Đầu tiên, kiểm tra Sao chép sang vị trí khác từ tùy chọn Hành động
• Bây giờ, trong hộp văn bản bên cạnh Phạm vi danh sách , đi tới Bản gốc tờ và chọn phạm vi để lọc (đối với tập dữ liệu của chúng tôi, phạm vi là B4:E12 ).

• Sau đó, trong hộp văn bản bên cạnh Phạm vi tiêu chí , chọn phạm vi tiêu chí ( John’s Marks nhỏ hơn 80 ) được lưu trữ trong Bản gốc tờ (đối với tập dữ liệu của chúng tôi, phạm vi là G4:H5 ).

• Bây giờ, trong hộp văn bản bên cạnh Sao chép vào , đi tới Đã lọc tờ , trang tính nơi bạn muốn lưu trữ dữ liệu đã sao chép và chọn phạm vi tiêu đề (đối với tập dữ liệu của chúng tôi, phạm vi là B4:E4 ).
• Cuối cùng, nhấp vào OK .

Để xem kết quả của toàn bộ quy trình này, hãy xem hình ảnh bên dưới. Chỉ dữ liệu trong đó John’s Marks nhỏ hơn 80 được sao chép trong Đã lọc tờ từ Bản gốc tờ với ghi macro .

• Bây giờ, nhấp vào ký hiệu macro ở dưới cùng bên trái của trang tính để dừng ghi macro . Giờ đây, bạn có một macro được ghi lại sẽ thực hiện quy trình trên được mô tả mỗi khi bạn chạy nó.

Nhưng có một nhược điểm của phương pháp này. Nếu bạn thêm dữ liệu mới vào Bản gốc tờ , Đã lọc tờ sẽ không được cập nhật ngay cả khi dữ liệu đáp ứng các tiêu chí.

Chúng tôi muốn Được lọc mới của chúng tôi tờ được cập nhật tự động bằng cách thực thi mã khi chúng tôi thêm dữ liệu mới vào Bản gốc tờ . Để làm điều đó, chúng tôi cần sửa đổi mã một chút.

Tất cả các bước để đạt được những gì chúng tôi muốn được hiển thị bên dưới.

Các bước:

• Lúc đầu, từ tab, hãy chọn Xem -> Macro -> Xem Macro .

• Sau đó, một Macro cửa sổ bật lên sẽ xuất hiện. Chọn Tên macro mà bạn vừa tạo bằng cách ghi âm ( AdvancedFilter đối với trường hợp của chúng tôi).
• Sau đó, nhấp vào Chỉnh sửa .

• Mã đằng sau macro đã ghi sẽ xuất hiện trong cửa sổ mã (xem hình ảnh bên dưới).

• Bây giờ, xóa phần được đánh dấu màu xanh lam (hiển thị trong hình bên dưới) từ mã.

• Sau đó, sửa đổi mã như trong hình sau.

• Bây giờ, mã được 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 Bản gốc tờ và thêm dữ liệu mới sẽ thuộc tiêu chí. Ví dụ:chúng tôi đã thêm một hàng khác của John Thông tin của Marks thu được là 76 thuộc tiêu chí Đánh dấu dưới 80 .

• Sau đó, Chạy mã và xem hình ảnh sau để biết kết quả.

• Có một hàng mới được sao chép trong Đã lọc tờ của John Thông tin của Marks 76 đáp ứng các tiêu chí ( Điểm <80 ).

Đọc thêm:Cách sử dụng bộ lọc nâng cao chỉ dành cho bản ghi duy nhất trong Excel

Kết luận

Để kết thúc, 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 một trang tính khác bằng 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 hữu ích cho bạn. Vui lòng đặt bất kỳ câu hỏi nào liên quan đến chủ đề này.

Các bài viết có liên quan

• Cách Sử dụng Bộ lọc Nâng cao để Loại trừ Ô trống trong Excel (3 Thủ thuật 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 Chỉ dành cho 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
• Bộ lọc Nâng cao của Excel:Á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