Цей підручник покаже вам, як використовувати PasteSpecial у VBA для вставлення лише певних властивостей комірки (наприклад, значення, формати)
В Excel під час копіювання та вставлення комірки ви копіюєте та вставляєте усі властивості клітинки: значення, формати, формули, форматування чисел, межі тощо:
Замість цього ви можете "Вставити спеціально", щоб вставити лише певні властивості клітинки. У програмі Excel спеціальне меню вставки можна отримати за допомогою комбінації клавіш CTRL + ALT + V (після копіювання комірки):
Тут ви можете побачити всі комбінації властивостей клітинок, які можна вставити.
Якщо ви записуєте макрос під час використання спеціального меню Paste, ви можете просто скористатися згенерованим кодом. Часто це найпростіший спосіб використовувати VBA для спеціальної вставки.
Вставити значення
Вставити значення лише вставляє клітинку "значення". Якщо клітинка містить формулу, Вставити значення вставить результат формули.
Цей код буде копіювати та вставляти значення для однієї клітинки на одному аркуші:
| 12 | Діапазон ("A1"). КопіюватиДіапазон ("B1"). PasteSpecial Paste: = xlPasteValues |
Копіювати та вставити значення на інший аркуш
У цьому прикладі буде скопійовано та вставлено значення для окремих клітинок на різних аркушах
| 12 | Аркуші ("Аркуш1"). Діапазон ("А1"). КопіюватиАркуші ("Sheet2"). Діапазон ("B1"). PasteSpecial Paste: = xlPasteValues |
У цих прикладах буде скопійовано та вставлено значення для діапазонів клітинок:
Діапазони копіювання та вставки значень
| 12 | Діапазон ("A1: B3"). КопіюватиДіапазон ("C1"). PasteSpecial Paste: = xlPasteValues |
Копіювати та вставити значення стовпців
| 12 | Стовпці ("А"). КопіюватиСтовпці ("B"). PasteSpecial Paste: = xlPasteValues |
Рядки копіювання та вставлення значень
| 12 | Ряди (1). КопіюватиРядки (2). PasteSpecial Paste: = xlPasteValues |
Вставте значення та формати номерів
Вставляючи значення, буде вставлено лише значення комірки. Не вставляється форматування, включаючи форматування чисел.
Часто, коли ви вставляєте значення, ви, ймовірно, захочете включити також форматування чисел, щоб ваші значення залишалися відформатованими. Розглянемо приклад.
Тут ми будемо мати значення вставити клітинку, що містить відсоток:
| 12 | Аркуші ("Аркуш1"). Стовпці ("D"). КопіюватиАркуші ("Аркуш2"). Стовпці ("В"). ВставитиСпеціальна вставка: = xlPasteValues |
Зверніть увагу, як втрачається форматування відсоткового числа, і замість цього відображається недбале десяткове значення.
Замість цього скористаємося форматами вставлення значень і чисел:
| 12 | Аркуші ("Аркуш1"). Стовпці ("D"). КопіюватиАркуші ("Sheet2"). Стовпці ("B"). PasteSpecial Paste: = xlPasteValuesAndNumberFormats |
Тепер ви можете бачити, що форматування чисел також вставляється, зберігаючи формат у відсотках.
.Цінність замість .Паста
Замість вставлення значень можна використати властивість Value об’єкта Range:
Це встановить значення комірки А2 рівним значенню комірки В2
| 1 | Діапазон ("A2"). Значення = Діапазон ("B2"). Значення |
Ви також можете встановити діапазон клітинок, що дорівнює значенню однієї клітини:
| 1 | Діапазон ("A2: C5"). Значення = Діапазон ("A1"). Значення |
або діапазон клітинок, що дорівнює іншому діапазону клітин ідентичного розміру:
| 1 | Діапазон ("B2: D4"). Значення = Діапазон ("A1: C3"). Значення |
Для використання властивості Value менше введення тексту. Крім того, якщо ви хочете володіти Excel VBA, вам слід знати роботу з властивістю Value клітинок.
Значення клітинки проти властивості Value2
Технічно краще використовувати властивість Value2 клітинки. Значення 2 трохи швидше (це має значення лише для дуже великих обчислень), а властивість Значення може дати вам усічений результат, клітинка відформатована як валюта або дата. Однак 99%+ коду, який я бачив, використовує .Value, а не .Value2. Я особисто не використовую .Value2, але ви повинні знати, що він існує.
| 1 | Діапазон ("A2"). Значення2 = Діапазон ("B2"). Значення2 |
Конструктор копіювання вставок
Ми створили “Копіювати вставку коду для вставки”, яка полегшує створення коду VBA для копіювання (або вирізання) та вставлення клітинок. Будівельник є частиною нашого Надбудова VBA: AutoMacro.
AutoMacro також містить багато інших Генератори коду, обширна Бібліотека кодів, і потужний Інструменти кодування.
Спеціальна вставка - Формати та формули
Крім значень вставки, найпоширенішими спеціальними параметрами вставки є формати вставки та формули вставки
Формати вставлення
Вставити формати дозволяє вставити все форматування комірок.
| 12 | Діапазон ("A1: A10"). КопіюватиДіапазон ("B1: B10"). PasteSpecial Paste: = xlPasteFormats |
Вставити формули
Вставити формули вставлять лише формули комірок. Це також надзвичайно корисно, якщо ви хочете скопіювати формули комірок, але не хочете копіювати кольори фону клітинки (або інше форматування комірок).
| 12 | Діапазон ("A1: A10"). КопіюватиДіапазон ("B1: B10"). PasteSpecial Paste: = xlPasteFormulas |
Вставити формули та формати чисел
Подібно до вищевказаних значень та форматів чисел, ви також можете копіювати та вставляти формати чисел разом із формулами
Тут ми скопіюємо формулу комірки лише з форматуванням облікового номера та формулами вставки.
| 12 | Аркуші ("Аркуш1"). Діапазон ("D3"). КопіюватиАркуші ("Sheet2"). Діапазон ("D3"). PasteSpecial xlPasteFormulas |
Зверніть увагу, як втрачається форматування чисел і замість цього відображається неохайне неокруглене значення.
Натомість давайте використовувати Формати формул та чисел:
| 12 | Аркуші ("Аркуш1"). Діапазон ("D3"). КопіюватиАркуші ("Sheet2"). Діапазон ("D3"). PasteSpecial xlPasteFormulasAndNumberFormats |
Тепер ви можете бачити, що форматування номерів також вставляється, зберігаючи формат обліку.
Спеціальна вставка - транспонування та пропуск пробілів
Спеціальна вставка - Транспонування
Спеціальне вставлення дозволяє копіювати та вставляти клітинки, змінюючи орієнтацію зверху знизу наліво-праворуч (або навпаки):
| 12 | Аркуші ("Аркуш1"). Діапазон ("А1: А5"). КопіюватиАркуші ("Аркуш1"). Діапазон ("В1"). Вставити Спеціальне транспонування: = Істина |
Спеціальна вставка - пропустіть пробіли
Пропустіть заготовки - це особливий варіант вставлення, який, здається, використовується не так часто, як слід. Вона дозволяє копіювати лише порожні клітинки під час копіювання та вставки. Тому порожні клітинки не копіюються.
У цьому прикладі нижче. Ми скопіюємо стовпець A, зробимо звичайну вставку у стовпець B і пропустимо вставку порожніх стовпців у стовпець C. Ви можете побачити, що порожні клітинки не були вставлені у стовпець C на зображенні нижче.
| 123 | Аркуші ("Аркуш1"). Діапазон ("А1: А5"). КопіюватиАркуші ("Аркуш1"). Діапазон ("В1"). Вставити Спеціальні пропуски: = НеправдаАркуші ("Аркуш1"). Діапазон ("С1"). Вставити Спеціальні пропуски: = Істина |
Інші опції вставки
Спеціальна вставка - Коментарі
| 1 | Аркуші ("Аркуш1"). Діапазон ("А1"). Копіювати аркуші ("Аркуш1"). Діапазон ("Е1"). ВставитиСпеціальні xlPasteComments |
Спеціальна вставка - перевірка
| 12 | Аркуші ("Аркуш1"). Діапазон ("А1: А4"). КопіюватиАркуші ("Sheet1"). Діапазон ("B1: B4"). PasteSpecial xlPasteValidation |
Спеціальна вставка - усі з використанням вихідної теми
| 123 | Робочі зошити (1). Аркуші ("Аркуш1"). Діапазон ("А1: А2"). КопіюватиРобочі зошити (2). Аркуші ("Аркуш1"). Діапазон ("А1"). ВставитиРобочі зошити (2) .Таблиці ("Аркуш1"). Діапазон ("В1"). ВставитиСпеціальні xlPasteAllUsingSourceTheme |
Спеціальна вставка - усі, крім кордонів
| 123 | Діапазон ("B2: C3"). КопіюватиДіапазон ("E2"). PasteSpecialДіапазон ("H2"). PasteSpecial xlPasteAllExceptBorders |
PasteSpecial - ширини стовпців
Мій особистий фаворит. Ширина стовпців PasteSpecial буде копіювати та вставляти ширину стовпців.
| 123 | Діапазон ("A1: A2"). КопіюватиДіапазон ("C1"). PasteSpecialДіапазон ("E1"). PasteSpecial xlPasteColumnWidths |
PasteSpecial - усі формати MergingConditionalFormat
| 123 | Діапазон ("A1: A4"). КопіюватиДіапазон ("C1"). PasteSpecialДіапазон ("E1"). PasteSpecial xlPasteAllMergingConditionalFormats |
