Tự động copy hàng sang sheet excel khác

Nếu bạn đang tìm cách di chuyển các hàng dữ liệu từ trang tính này sang trang tính khác tùy thuộc vào giá trị trong một ô nhất định, thì rất tiếc, không có tính năng hoặc mục menu trực tiếp nào cho nó trên Excel

Tuy nhiên, có một số cách mà chúng tôi có thể đề xuất cho bạn để hoàn thành việc này

Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn hai cách để di chuyển một hàng sang một trang tính khác dựa trên giá trị ô trong Excel

• Sử dụng bộ lọc Excel
• Sử dụng Macro VBA đơn giản

Mục lục

Hai cách để di chuyển hàng sang trang tính khác dựa trên giá trị ô trong Excel

Có hai cách để di chuyển hàng sang trang tính khác dựa trên giá trị ô

Phương pháp đầu tiên là một phương pháp thủ công hơn, bao gồm việc sử dụng các bộ lọc để trích xuất các hàng khớp với một giá trị ô nhất định

Sau đó, bạn có thể chọn các giá trị được trích xuất này, sao chép và dán chúng theo cách thủ công vào trang tính cần thiết của mình

Phương pháp thứ hai liên quan đến việc sử dụng tập lệnh VBA tự động di chuyển các hàng đến trang tính bạn yêu cầu ngay lập tức bất cứ khi nào nó khớp với một giá trị ô nhất định

Đối với cả hai phương pháp, chúng tôi sẽ xem xét tập dữ liệu sau

Mỗi hàng trong bộ dữ liệu này chứa thông tin chi tiết về một khách hàng

Ô cuối cùng của mỗi hàng chứa trạng thái giao dịch với khách hàng. Vì vậy, nếu giao dịch với khách hàng đang chờ xử lý, Trạng thái sẽ là 'Đang chờ xử lý'

Bây giờ, chúng tôi muốn rằng khi giao dịch với một khách hàng cụ thể là 'Đã đóng', thông tin chi tiết về khách hàng (hoặc hàng chứa thông tin chi tiết về khách hàng) được chuyển sang một trang tính riêng, chẳng hạn như Trang tính 2

Chúng ta hãy xem hai cách mà chúng ta có thể thực hiện điều này

Sử dụng Bộ lọc để Di chuyển Hàng sang Trang tính khác dựa trên Giá trị Ô trong Excel

Bộ lọc Excel được sử dụng để trích xuất các bản ghi phù hợp với tiêu chí đã chỉ định

Vì vậy, chúng có thể thực sự hữu ích khi bạn muốn hiển thị các bản ghi phù hợp với dữ liệu của mình trong khi ẩn các bản ghi khác

Để sử dụng các bộ lọc trên tập dữ liệu mẫu của chúng tôi, hãy làm theo các bước bên dưới

1. Chọn toàn bộ tập dữ liệu
2. Nhấp vào tab Dữ liệu
3. Nhấp vào biểu tượng Bộ lọc (trong nhóm ‘Sắp xếp và Lọc’)

1. Bạn sẽ thấy rằng tất cả các ô tiêu đề hiện có một mũi tên nhỏ hướng xuống dưới. Nhấp vào mũi tên bên cạnh tiêu đề Trạng thái

1. Từ danh sách thả xuống xuất hiện, đảm bảo rằng chỉ hộp bên cạnh “Đã đóng” được chọn.  

1. Nhấp vào OK. Giờ đây, bạn sẽ chỉ thấy những hàng có Trạng thái là  “Đã đóng”

1. Chọn tất cả các hàng trong dạng xem (ngoại trừ hàng tiêu đề) và nhấn Alt+;
2. Nhấp chuột phải và chọn “Sao chép” từ menu bật lên hoặc chỉ cần nhấn CTRL + C trên bàn phím. Điều này sẽ chỉ sao chép tất cả các hàng hiển thị.  
3. Chọn tab tương ứng với trang tính mới (trong ví dụ của chúng tôi là Sheet2) và dán tab đó vào đó bằng cách nhấn CTRL+V

1. Quay lại trang tính ban đầu của bạn và nhấp chuột phải vào các hàng hiển thị đã chọn. Nhấp vào tùy chọn Xóa. Thao tác này sẽ xóa các hàng hiển thị khỏi trang tính hiện tại (Sheet1)

1. Nhấp vào nút Bộ lọc từ tab Dữ liệu một lần nữa để xóa bộ lọc

Bây giờ, bạn sẽ thấy tất cả dữ liệu của mình, không có hàng tương ứng với bất kỳ ứng dụng khách "Đã đóng" nào

Lưu ý rằng toàn bộ quá trình này là một phần thủ công vì bạn phải sao chép và dán các hàng đã lọc ra vào trang tính mới

Hơn nữa, quá trình này không xảy ra trong thời gian thực

Vì vậy, trong trường hợp bạn thêm một bản ghi mới trong đó trạng thái máy khách đã đóng, bạn sẽ phải lặp lại toàn bộ quy trình và sao chép thủ công các hàng đã lọc vào trang tính khác

Tôi biết đây không phải là cách tốt nhất, nhưng nó nhanh chóng và hoàn thành công việc. Sử dụng phương pháp này nếu bạn có tập dữ liệu không cập nhật (vì vậy tốt nhất bạn chỉ phải thực hiện việc này một hoặc vài lần).  

Cũng lưu ý rằng trong ví dụ này, tôi chỉ sử dụng giá trị ô trong một cột (cột trạng thái). Bạn cũng có thể làm điều này cho nhiều giá trị ô. Ví dụ: di chuyển tất cả các máy khách ở nơi đóng trạng thái và khu vực ở Bắc Mỹ. Đối với điều này, bạn chỉ cần áp dụng hai bộ lọc, sau đó sao chép và dán các bản ghi

Trong ví dụ trên, chúng tôi đã sao chép các bản ghi sang một trang tính khác, vì vậy các bản ghi này vẫn nằm trong tập dữ liệu gốc. Trong trường hợp bạn muốn những thứ này di chuyển hoàn toàn (tôi. e. , không phải là một phần của tập dữ liệu gốc), thay vì sao chép-dán, hãy sử dụng cắt-dán

Bây giờ, nếu bạn có thể xử lý một chút VBA và tự động hóa, hãy để tôi chỉ cho bạn một cách khác để di chuyển các hàng sang một trang tính riêng biệt dựa trên giá trị ô

Sử dụng VBA để Di chuyển Hàng sang Trang tính khác dựa trên Giá trị Ô trong Excel

Cách thứ hai và hiệu quả hơn để di chuyển một hàng sang một trang tính khác dựa trên giá trị ô là sử dụng mã macro VBA đơn giản

Trong phần này, chúng tôi sẽ cung cấp một vài dòng mã VBA của Excel để giúp bạn thực hiện việc này

Chúng tôi cũng sẽ giải thích mã và cách mã hoạt động để bạn có thể tùy chỉnh mã theo yêu cầu của riêng mình

Giả sử bạn có tập dữ liệu được hiển thị bên dưới và bạn muốn di chuyển tất cả các bản ghi có trạng thái là 'Đã đóng' sang một trang tính khác

Tôi sẽ đề cập đến hai kịch bản trong phần này

• Mã VBA để Di chuyển một hàng sang một trang tính khác và xóa nó khỏi tập dữ liệu gốc
• Mã VBA để Di chuyển một hàng sang một trang tính khác và cũng giữ nó trong tập dữ liệu gốc

Mã VBA (Di chuyển hàng sang trang tính khác và xóa khỏi dữ liệu gốc)

Đây là mã mà chúng ta sẽ sử dụng

'VBA Code by Scott from SpreadsheetPlanet.com
Sub move_rows_to_another_sheet()
For Each myCell In Selection.Columns(4).Cells
If myCell.Value = "Closed" Then
myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
myCell.EntireRow.Delete
End If
Next
End Sub

Đoạn mã này sẽ sao chép toàn bộ hàng tương ứng với giá trị ô chứa từ ‘Closed’’ vào bảng tính có tên ‘Sheet2’. Ngoài ra, các bản ghi có trạng thái đóng sẽ bị xóa khỏi tập dữ liệu gốc

Để chạy mã này

Nếu tên trang tính của bạn là tên khác, bạn có thể thay đổi mã VBA (thay đổi tên trang tính ở dòng thứ 4 của mã)

Hàng đã di chuyển sẽ được dán ngay sau hàng cuối cùng hiện tại trong trang đích

Mã VBA (Chuyển hàng sang trang tính khác và giữ nguyên dữ liệu gốc)

Mặc dù đoạn mã trên sẽ di chuyển tất cả các bản ghi cho dù trạng thái đã đóng và cũng xóa nó khỏi tập dữ liệu gốc, nhưng bên dưới là đoạn mã sẽ không xóa dữ liệu khỏi tập dữ liệu gốc

'VBA Code by Scott from SpreadsheetPlanet.com
Sub move_rows_to_another_sheet()
For Each myCell In Selection.Columns(4).Cells
If myCell.Value = "Closed" Then
myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
End If
Next
End Sub

Thay đổi duy nhất tôi đã thực hiện ở đây là xóa dòng mã sau

di động của tôi. Toàn bộ hàng. Xóa bỏ

Bây giờ tôi đã cung cấp cho bạn mã VBA cho cả hai kịch bản, hãy để tôi chỉ cho bạn vị trí đặt mã này và cách chạy nó,

Đặt mã VBA ở đâu

Để sử dụng đoạn mã trên, tất cả những gì bạn cần làm là sao chép và dán mã đó vào cửa sổ VBA

Đây là cách

1. Nhấp vào tùy chọn Nhà phát triển trong ruy-băng
2. Bấm vào Visual Basic. Ths sẽ mở cửa sổ VBA
3. Trong chương trình phụ trợ VBA mở ra, bạn sẽ thấy tất cả các tệp và thư mục dự án của mình trong Project Explorer ở bên trái
4. Từ Menu Chèn, chọn tùy chọn Mô-đun

1. Điều này sẽ mở ra một cửa sổ mô-đun mới cho trang tính đã chọn của bạn
2. Bây giờ bạn có thể bắt đầu mã hóa. Sao chép các dòng mã trên và dán chúng vào cửa sổ mô-đun trống

1. Đóng cửa sổ VBA

Chạy Macro

Bây giờ macro của bạn đã sẵn sàng, bạn có thể tiếp tục và chạy nó

Chọn khu vực chứa dữ liệu của bạn trong trang tính gốc

Trong ví dụ của chúng tôi, chọn phạm vi A2. D11 của Sheet1

Tiếp theo, chọn Macro từ menu Nhà phát triển

Thao tác này sẽ mở hộp thoại Macro. Chọn Tên Macro. di chuyển_rows_to_another_sheet và nhấp vào Chạy

Nếu bạn đã sử dụng mã đầu tiên (xóa bản ghi được di chuyển), dữ liệu cuối cùng của bạn sẽ trông như thế này

Lưu ý rằng các hàng tương ứng với “David Jones” và “Cierra Vega” đã biến mất vì chúng có Trạng thái=”Đã đóng”

Hai hàng đã bị xóa khỏi Sheet1 giờ đã được chuyển sang Sheet2

Giải thích về Bộ luật

Hãy để chúng tôi dành vài phút để hiểu mã này để bạn có thể tùy chỉnh nó theo yêu cầu của mình

Mã lặp qua từng ô trong cột “4” (hoặc cột D) của trang tính

For Each myCell In Selection.Columns(4).Cells

Nếu ô nào chứa giá trị “Closed” thì ta copy toàn bộ hàng tương ứng với ô đó vào Sheet2 (tại cột A, và hàng sau hàng cuối cùng trong sheet)

If myCell.Value = “Closed” Then
myCell.EntireRow.Copy Sheet2.Range(“A” & Rows.Count).End(3)(2)
We then delete the copied row from Sheet1
myCell.EntireRow.Delete

Ghi chú. Nếu bạn chỉ muốn gửi một bản sao của các hàng tới Sheet2 mà không xóa chúng khỏi Sheet1, thì bạn chỉ cần bỏ qua dòng này khỏi mã

Bạn có thể tiếp tục và điều chỉnh mã này theo tập dữ liệu của mình. Chỉ cần thay thế Cột số 4 (ở dòng 2) bằng số cột bạn cần phân tích và thay thế giá trị “Đã đóng” (ở dòng 3) bằng giá trị ô bạn cần làm bộ định tính để di chuyển hàng

Bạn cũng có thể thay đổi tên trang tính từ “Sheet2” (ở dòng 4) thành tên trang tính đích của bạn

Trong hướng dẫn này, tôi đã chỉ cho bạn cách sử dụng kỹ thuật Bộ lọc đơn giản và mã macro VBA đơn giản để di chuyển hàng sang trang tính khác dựa trên giá trị ô

Mặc dù phương pháp đầu tiên thủ công hơn, nhưng phương pháp thứ hai sử dụng mã VBA tự động và nhanh hơn rất nhiều

Với mã VBA, bạn có thể chỉ cần chọn phạm vi ô chứa dữ liệu của mình và di chuyển tất cả các hàng chứa một giá trị ô cụ thể sang một trang tính khác bằng cách chạy macro

Làm cách nào để Copy dữ liệu trong Excel từ sheet này sang sheet khác một cách tự động?