Hướng dẫn này sẽ chỉ cho bạn cách sử dụng PasteSpecial trong VBA để chỉ dán các thuộc tính ô nhất định (ví dụ: giá trị, định dạng)
Trong Excel, khi bạn sao chép và dán một ô, bạn sẽ sao chép và dán tất cả các thuộc tính của ô đó: giá trị, định dạng, công thức, định dạng số, đường viền, v.v.:
Thay vào đó, bạn có thể “Dán đặc biệt” để chỉ dán các thuộc tính ô nhất định. Trong Excel, menu Dán Đặc biệt có thể được truy cập bằng phím tắt CTRL + ALT + V (sau khi sao chép một ô):
Tại đây, bạn có thể xem tất cả các tổ hợp thuộc tính ô mà bạn có thể dán.
Nếu bạn ghi macro trong khi sử dụng Menu Dán Đặc biệt, bạn có thể chỉ cần sử dụng mã đã tạo. Đây thường là cách dễ nhất để sử dụng VBA để Dán Đặc biệt.
Dán các giá trị
Dán Giá trị chỉ dán ô “giá trị”. Nếu ô chứa công thức, Giá trị Dán sẽ dán kết quả công thức.
Mã này sẽ Sao chép & Dán Giá trị cho một ô trên cùng một trang tính:
| 12 | Phạm vi ("A1"). Sao chépPhạm vi ("B1"). PasteSpecial Paste: = xlPasteValues |
Sao chép và dán giá trị sang trang tính khác
Ví dụ này sẽ Sao chép & Dán Giá trị cho các ô đơn trên các trang tính khác nhau
| 12 | Trang tính ("Trang 1"). Phạm vi ("A1"). Sao chépTrang tính ("Sheet2"). Phạm vi ("B1"). PasteSpecial Paste: = xlPasteValues |
Các ví dụ này sẽ Sao chép & Dán Giá trị cho một phạm vi ô:
Sao chép và dán giá trị phạm vi
| 12 | Phạm vi ("A1: B3"). Sao chépPhạm vi ("C1"). PasteSpecial Paste: = xlPasteValues |
Sao chép và dán giá trị các cột
| 12 | Các cột ("A"). Sao chépCột ("B"). PasteSpecial Paste: = xlPasteValues |
Sao chép và dán giá trị hàng
| 12 | Hàng (1). Sao chépHàng (2) .PasteSpecial Paste: = xlPasteValues |
Dán các giá trị và định dạng số
Dán Giá trị sẽ chỉ dán giá trị ô. Không có Định dạng nào được dán, kể cả Định dạng Số.
Thông thường, khi bạn Dán Giá trị, bạn có thể sẽ muốn bao gồm cả định dạng số để các giá trị của bạn vẫn được định dạng. Hãy xem một ví dụ.
Ở đây chúng tôi sẽ định giá trị dán một ô có chứa phần trăm:
| 12 | Trang tính ("Trang 1"). Cột ("D"). Sao chépTrang tính ("Sheet2"). Cột ("B"). PasteSpecial Paste: = xlPasteValues |
Lưu ý cách định dạng số phần trăm bị mất và thay vào đó một giá trị thập phân cẩu thả được hiển thị.
Thay vào đó, hãy sử dụng các định dạng Dán Giá trị và Số:
| 12 | Trang tính ("Trang 1"). Cột ("D"). Sao chépTrang tính ("Sheet2"). Cột ("B"). PasteSpecial Paste: = xlPasteValuesAndNumberFormats |
Bây giờ bạn có thể thấy định dạng số cũng được dán lên, duy trì định dạng phần trăm.
.Value thay vì .Paste
Thay vì Dán Giá trị, bạn có thể sử dụng thuộc tính Giá trị của đối tượng Phạm vi:
Điều này sẽ đặt giá trị ô của A2 bằng giá trị ô của B2
| 1 | Phạm vi ("A2"). Giá trị = Phạm vi ("B2"). Giá trị |
Bạn cũng có thể đặt một dải ô bằng giá trị của một ô:
| 1 | Phạm vi ("A2: C5"). Giá trị = Phạm vi ("A1"). Giá trị |
hoặc một dải ô bằng một dải ô có kích thước giống hệt nhau khác:
| 1 | Phạm vi ("B2: D4"). Giá trị = Phạm vi ("A1: C3"). Giá trị |
Việc sử dụng thuộc tính Giá trị sẽ ít hơn. Ngoài ra, nếu bạn muốn trở nên thành thạo với Excel VBA, bạn nên làm quen với việc làm việc với thuộc tính Giá trị của ô.
Giá trị ô so với Thuộc tính Value2
Về mặt kỹ thuật, tốt hơn là sử dụng thuộc tính Value2 của một ô. Value2 nhanh hơn một chút (điều này chỉ quan trọng với các phép tính cực lớn) và thuộc tính Value có thể cung cấp cho bạn kết quả cắt ngắn của ô được định dạng là tiền tệ hoặc ngày. Tuy nhiên, hơn 99% mã mà tôi đã thấy sử dụng .Value chứ không phải .Value2. Cá nhân tôi không sử dụng .Value2, nhưng bạn nên biết rằng nó tồn tại.
| 1 | Phạm vi ("A2"). Giá trị2 = Phạm vi ("B2"). Giá trị2 |
Copy Paste Builder
Chúng tôi đã tạo “Trình tạo mã sao chép dán” giúp dễ dàng tạo mã VBA để sao chép (hoặc cắt) và dán các ô. Người xây dựng là một phần của Bổ trợ VBA: AutoMacro.
AutoMacro cũng chứa nhiều khác Trình tạo mã, Rộng Thư viện mãvà mạnh mẽ Công cụ mã hóa.
Dán Đặc biệt - Định dạng và Công thức
Bên cạnh Giá trị Dán, các tùy chọn Dán Đặc biệt phổ biến nhất là Dán Định dạng và Dán Công thức
Dán các định dạng
Định dạng dán cho phép bạn dán tất cả các định dạng ô.
| 12 | Phạm vi ("A1: A10"). Sao chépPhạm vi ("B1: B10"). PasteSpecial Paste: = xlPasteFormats |
Dán công thức
Dán công thức sẽ chỉ dán các công thức ô. Điều này cũng cực kỳ hữu ích nếu bạn muốn sao chép công thức ô nhưng không muốn sao chép màu nền của ô (hoặc định dạng ô khác).
| 12 | Phạm vi ("A1: A10"). Sao chépPhạm vi ("B1: B10"). Dán Đặc biệt: = xlPasteFormulas |
Dán công thức và định dạng số
Tương tự như Dán Giá trị và Định dạng Số ở trên, bạn cũng có thể sao chép và dán các định dạng số cùng với các công thức
Ở đây, chúng tôi sẽ sao chép một công thức ô chỉ với Định dạng Số Kế toán và Dán Công thức.
| 12 | Trang tính ("Trang 1"). Phạm vi ("D3"). Sao chépTrang tính ("Sheet2"). Phạm vi ("D3"). PasteSpecial xlPasteFormulas |
Lưu ý cách định dạng số bị mất và thay vào đó một giá trị không làm tròn cẩu thả được hiển thị.
Thay vào đó, hãy sử dụng các định dạng Dán Công thức và Số:
| 12 | Trang tính ("Trang 1"). Phạm vi ("D3"). Sao chépTrang tính ("Sheet2"). Phạm vi ("D3"). PasteSpecial xlPasteFormulasAndNumberFormats |
Bây giờ bạn có thể thấy định dạng số cũng được dán lên, duy trì định dạng Kế toán.
Dán đặc biệt - Transpose và bỏ qua khoảng trống
Dán đặc biệt - Transpose
Dán Transpose đặc biệt cho phép bạn sao chép và dán các ô thay đổi hướng từ trên xuống dưới sang trái-phải (hoặc ngược lại):
| 12 | Trang tính ("Trang 1"). Phạm vi ("A1: A5"). Sao chépTrang tính ("Trang 1"). Phạm vi ("B1"). PasteSpecial Transpose: = True |
Dán đặc biệt - Bỏ qua khoảng trống
Bỏ qua khoảng trống là một tùy chọn dán đặc biệt dường như không được sử dụng thường xuyên. Nó cho phép bạn chỉ sao chép các ô không trống khi sao chép và dán. Vì vậy, các ô trống không được sao chép.
Trong ví dụ này dưới đây. Chúng tôi sẽ sao chép cột A, thực hiện dán thông thường vào cột B và bỏ qua các ô trống dán vào cột C. Bạn có thể thấy các ô trống không được dán vào cột C trong hình ảnh bên dưới.
| 123 | Trang tính ("Trang 1"). Phạm vi ("A1: A5"). Sao chépTrang tính ("Trang 1"). Phạm vi ("B1"). PasteSpecial SkipBlanks: = FalseTrang tính ("Trang 1"). Phạm vi ("C1"). PasteSpecial SkipBlanks: = True |
Tùy chọn đặc biệt dán khác
Dán Đặc biệt - Nhận xét
| 1 | Trang tính ("Trang tính1"). Phạm vi ("A1"). Sao chép Trang tính ("Trang tính1"). Phạm vi ("E1"). PasteSpecial xlPasteComments |
Dán Đặc biệt - Xác thực
| 12 | Trang tính ("Trang 1"). Phạm vi ("A1: A4"). Sao chépTrang tính ("Sheet1"). Phạm vi ("B1: B4"). PasteSpecial xlPasteValidation |
Dán đặc biệt - Tất cả đều sử dụng chủ đề nguồn
| 123 | Workbooks (1) .Sheets ("Sheet1"). Phạm vi ("A1: A2"). Sao chépWorkbooks (2) .Sheets ("Sheet1"). Phạm vi ("A1"). PasteSpecialWorkbooks (2) .Sheets ("Sheet1"). Range ("B1"). PasteSpecial xlPasteAllUsingSourceTheme |
Dán Đặc biệt - Tất cả Ngoại trừ Viền
| 123 | Phạm vi ("B2: C3"). Sao chépPhạm vi ("E2"). PasteSpecialPhạm vi ("H2"). PasteSpecial xlPasteAllExceptBorders |
PasteSpecial - Độ rộng cột
Một yêu thích cá nhân của tôi. PasteSpecial Column Widths sẽ sao chép và dán chiều rộng của các cột.
| 123 | Phạm vi ("A1: A2"). Sao chépPhạm vi ("C1"). PasteSpecialPhạm vi ("E1"). PasteSpecial xlPasteColumnWidths |
PasteSpecial - All MergingConditionalFormats
| 123 | Phạm vi ("A1: A4"). Sao chépPhạm vi ("C1"). PasteSpecialPhạm vi ("E1"). PasteSpecial xlPasteAllMergingConditionalFormats |
