Cách sao chép dữ liệu từ trang tính Excel này sang trang khác bằng công thức
Câu hỏi thường gặp này sẽ hướng dẫn bạn cách đủ điều kiện và di chuyển dữ liệu của bạn.
Sao chép dữ liệu từ trang tính Excel này sang trang khác bằng công thức
Bắt đầu bằng cách mở tab nhà phát triển trong Excel.Tiếp theo, bạn cần xây dựng UserForm của Trình điều khiển Trang tính. UserForm này cho phép bạn chọn nơi di chuyển dữ liệu, nơi dữ liệu được di chuyển đến, cột nào cần kiểm tra dữ liệu đủ điều kiện để xác định xem nó có di chuyển hay không và giá trị của mục nhập cột sẽ được di chuyển.
Khi tạo UserForm của bạn, hãy phản chiếu ví dụ sau, chú ý đặt tên cho từng điều khiển một cách chính xác:
Bây giờ bạn đã xây dựng Biểu mẫu người dùng, bạn cần đính kèm mã cho mỗi điều khiển. Trong UserForm, bấm đúp vào điều khiển có tên CommandButton2 . Sơ đồ của Biểu mẫu người dùng sẽ không còn hiển thị nữa và bây giờ bạn sẽ được trình bày với trình thám hiểm mã, với khối mã mặc định. Khối mã cụ thể này bắt đầu với Private Sub ComandButton2_Click () . Đặt con trỏ của bạn dưới dòng đầu tiên, nhưng trước dòng có chữ End Sub . Bây giờ, nhập mã sau đây để thiết lập các biến công khai sẽ được áp dụng cho phần còn lại của mã:
Bây giờ, hãy quay lại UserForm Explorer và bấm đúp vào điều khiển được đánh dấu CommandButton3 . Một lần nữa, UserForm được đặt sang một bên cho trình thám hiểm mã. Đặt con trỏ của bạn vào khối mã vừa tạo và nhập mã sau đây:
Trong phần Explorer của Project Explorer, bấm chuột phải vào Đối tượng Microsoft Excel . Chọn Chèn > Mô-đun .
Bấm đúp vào mô-đun được gọi là Module1 và nhập các biến công khai sau:
Bây giờ, hãy tiếp tục và chèn thêm ba tờ vào sổ làm việc của bạn. Bây giờ bạn sẽ có bốn trang tính, được đặt tên là Sheet1, Sheet2, Sheet3 và Sheet4 .
Trên Trang tính 1, đặt các mục vào khoảng 10-15 dòng, sử dụng hình ảnh bên dưới làm ví dụ về dữ liệu thử nghiệm:
Tiếp theo, trong tab Nhà phát triển (ở trên cùng của sổ làm việc), bấm Điều khiển > Chèn > biểu tượng nút :
Bây giờ, đặt nút bất cứ nơi nào trên tờ của bạn. Khi nó hỏi về macro, chọn Mới .
Bạn sẽ nhận thấy rằng nó đã đặt macro mới trong Module2 của dự án. Đánh dấu khối mã trong Module2, cắt nó từ Module2 . Bây giờ, bấm đúp vào Module1 . Khi Code Explorer mở, nhấp chuột phải và chọn Dán . Bây giờ, bạn nên có một khối mã trống có nội dung:
Đặt con trỏ của bạn bên trong khối mã và thêm đoạn mã sau:
Bạn sẽ nhận thấy rằng có một chức năng gọi là Buildform . Điều này thiết lập UserForm cho số lượng trang tính phù hợp, sau khi lấy hàng tồn kho của chúng. Để áp dụng điều này, hãy đặt đoạn mã sau vào Trình khám phá mã, bên dưới chương trình con Nút1_Click () :
Trong hàm buildform, có một hàm khác gọi là Counttabs . Bạn nên đặt mã này phía trên mã Buildform, nhưng bên dưới chương trình con Nút1_click :
Nếu cả hai biến TabFrom và TabTo được đặt, thì bạn sẽ cần chạy hàm createdNew () . Đặt mã bên dưới vào Trình khám phá mã, phía trên chương trình con Nút1_click :
Nếu bạn đã chọn tạo một trang tính mới, hãy thay đổi biến TabTo thành tên trang tính mới. Sau đó, bạn sẽ cần chạy thói quen LoopForMove (TabFrom, TabTo) . Trong Code Explorer, nhập mã sau đây:
Để tìm hàng cuối cùng của trang tính của bạn, hãy nhập mã dưới đây vào Code Explorer, phía trên LoopForMove (FromWhatSheet, ToWhatSheet) :
Bây giờ, bạn sẽ có thể di chuyển mã thực tế bằng cách sử dụng chức năng Moveit (FromWhatSheet, CellLoc, ToWhatSheet, CutVal) . Giữa các hàm LooForMove () và createNew (), đặt đoạn mã sau:
Tiếp tục lặp qua từng dòng trên trang tính From, tìm kiếm các mục đủ điều kiện, sử dụng chức năng For Loop .
Để tổng hợp các hành động của UserForm, hãy xem hình minh họa sau:
Đây là cùng một minh họa với, lần này, với nhiều yếu tố được xem xét:
Cuối cùng, đây là toàn bộ đoạn mã nói trên:
Tùy chọn rõ ràngTabFrom công khai
Tab công khai
Qualif dưới dạng chuỗi
WhatCol công cộng
Đăng nhập công cộng
CutVal công cộng
FormXcel công cộng
Hàm FindLastRow (OnWhatsheet)
FindLastRow = Cells (ThisWorkbook.Worksheet (OnWhatsheet) .Rows.Count, 1) .End (xlUp) .Row
Chức năng kết thúc
Chức năng LoopForMove (FromWhatSheet, ToWhatSheet)
Dim LastRow, Cnt
Dim CellValue dưới dạng chuỗi
Dim CellLoc
Dim
Nếu WhatCol = "" Sau đó
WhatCol = "A"
Kết thúc nếu
Nếu Qualif = "" Sau đó
Qualif = "X"
Kết thúc nếu
ThisWorkbook.Worksheet (FromWhatSheet) .Chọn
LastRow = FindLastRow (FromWhatSheet)
Đối với Cnt = LastRow đến 1 Bước -1
CellLoc = WhatCol & Cnt
CellValue = ThisWorkbook.Worksheet (FromWhatSheet) .Range (CellLoc) .Value
Nếu CellValue = Qualif thì
nret = Moveit (FromWhatSheet, CellLoc, ToWhatSheet, CutVal)
Kết thúc nếu
Kế tiếp
Chức năng kết thúc
Chức năng Moveit (FromSheet, WhatRange, ToWhere, CutVal)
Di chuyển DimSheetLastRow
Với ThisWorkbook.Worksheet (FromSheet)
.Lựa chọn
.Range (WhatRange) .EntireRow. Chọn
Kết thúc với
Lựa chọn. Bản sao
Nếu CutVal = Đúng thì
Lựa chọn. Nhưng
Kết thúc nếu
MoveSheetLastRow = FindLastRow (ToWhere)
ThisWorkbook.Worksheet (ToWhere) .Chọn
ThisWorkbook.Worksheet (ToWhere) .Cells (MoveSheetLastRow + 1, 1) .EntireRow.Select
Lựa chọn.Insert
ThisWorkbook.Worksheet (FromSheet) .Chọn
Application.CutCopyMode = Sai
Chức năng kết thúc
Hàm tạo New ()
Dim NewSheet
Nếu TabTo = "Trang tính mới" thì
ThisWorkbook.Sheets.Add After: = Sheets (Sheets.Count)
NewSheet = ThisWorkbook.ActiveSheet.Name
TabTo = NewSheet
Kết thúc nếu
Chức năng kết thúc
Nút phụ1_Click ()
Dim
bản dựng
Nếu FormXcel = Sai thì
Nếu TabFrom "" và TabTo "" Sau đó
tạo mới
nret = LoopForMove (TabFrom, TabTo)
Khác
MsgBox ("Vui lòng đặt trang tính 'Từ' và 'Đến'!")
Kết thúc nếu
Kết thúc nếu
Kết thúc phụ
Hàm Counttabs ()
Counttabs = ThisWorkbook.Worksheet.Count
Chức năng kết thúc
Hàm xây dựng ()
Tab Tab đếm
Trình điều khiển.ComboBox2.AddItem "Trang tính mới"
Đối với TabCount = 1 đến Counttabs
Trình điều khiển.ComboBox1.AddItem ThisWorkbook.Worksheet (TabCount) .Name
Trình điều khiển.ComboBox2.AddItem ThisWorkbook.Worksheet (TabCount) .Name
Kế tiếp
Trình điều khiển. Hiển thị
Chức năng kết thúc
Cảm ơn bạn ace3mark cho mẹo này.
Hình: © Microsoft.