Из этого туториала Вы узнаете, как использовать PasteSpecial в VBA для вставки только определенных свойств ячеек (например, значений, форматов).
В Excel, когда вы копируете и вставляете ячейку, вы копируете и вставляете все ее свойства: значения, форматы, формулы, форматирование чисел, границы и т. Д.
Вместо этого вы можете «Специальная вставка» вставить только определенные свойства ячеек. В Excel к меню Специальная вставка можно получить доступ с помощью сочетания клавиш CTRL + ALT + V (после копирования ячейки):
Здесь вы можете увидеть все комбинации свойств ячеек, которые вы можете вставить.
Если вы записываете макрос во время использования специального меню «Вставить», вы можете просто использовать сгенерированный код. Часто это самый простой способ использовать VBA для специальной вставки.
Вставить значения
Вставить значения - вставляет только «значение» ячейки. Если ячейка содержала формулу, функция «Вставить значения» вставит результат формулы.
Этот код скопирует и вставит значения для одной ячейки на том же листе:
12 | Диапазон ("A1"). КопироватьДиапазон ("B1"). PasteSpecial Paste: = xlPasteValues |
Копировать и вставлять значение на другой лист
В этом примере будут скопированы и вставлены значения для отдельных ячеек на разных листах.
12 | Листы ("Лист1"). Диапазон ("А1"). КопироватьЛисты ("Sheet2"). Range ("B1"). PasteSpecial Paste: = xlPasteValues |
Эти примеры будут копировать и вставлять значения для диапазонов ячеек:
Копирование и значение, вставка диапазонов
12 | Диапазон ("A1: B3"). КопироватьДиапазон ("C1"). PasteSpecial Paste: = xlPasteValues |
Копировать и вставлять значения столбцов
12 | Столбцы («А»). КопироватьСтолбцы ("B"). PasteSpecial Paste: = xlPasteValues |
Копировать и вставлять значения строк
12 | Ряды (1) .Копировать.Строки (2) .PasteSpecial Paste: = xlPasteValues |
Вставить значения и числовые форматы
При вставке значений будет вставлено только значение ячейки. Форматирование не вставляется, включая форматирование чисел.
Часто, когда вы вставляете значения, вы, вероятно, захотите также включить форматирование чисел, чтобы ваши значения оставались отформатированными. Давайте посмотрим на пример.
Здесь мы вставим значение ячейки, содержащей процент:
12 | Таблицы ("Sheet1"). Столбцы ("D"). КопироватьТаблицы ("Sheet2"). Столбцы ("B"). PasteSpecial Paste: = xlPasteValues |
Обратите внимание, как теряется форматирование процентного числа, и вместо него отображается неаккуратное десятичное значение.
Вместо этого давайте использовать форматы вставки значений и чисел:
12 | Таблицы ("Sheet1"). Столбцы ("D"). КопироватьЛисты ("Sheet2"). Столбцы ("B"). PasteSpecial Paste: = xlPasteValuesAndNumberFormats |
Теперь вы можете видеть, что форматирование чисел также вставлено с сохранением процентного формата.
.Value вместо .Paste
Вместо вставки значений вы можете использовать свойство Value объекта Range:
Это установит значение ячейки A2 равным значению ячейки B2.
1 | Диапазон ("A2"). Значение = Диапазон ("B2"). Значение |
Вы также можете установить диапазон ячеек, равный значению одной ячейки:
1 | Диапазон ("A2: C5"). Значение = Диапазон ("A1"). Значение |
или диапазон ячеек, равный другому диапазону ячеек такого же размера:
1 | Диапазон ("B2: D4"). Значение = Диапазон ("A1: C3"). Значение |
Использование свойства Value требует меньше усилий. Кроме того, если вы хотите освоить Excel VBA, вы должны быть знакомы с работой со свойством ячеек «Значение».
Значение ячейки и свойство Value2
Технически лучше использовать свойство Value2 ячейки. Value2 немного быстрее (это имеет значение только при очень больших вычислениях), а свойство Value может дать вам усеченный результат, если ячейка отформатирована как валюта или дата. Однако более 99% кода, который я видел, использует .Value, а не .Value2. Я лично не использую .Value2, но вы должны знать, что он существует.
1 | Диапазон ("A2"). Значение2 = Диапазон ("B2"). Значение2 |
Копировать Paste Builder
Мы создали «Copy Paste Code Builder», который упрощает создание кода VBA для копирования (или вырезания) и вставки ячеек. Строитель является частью нашей Надстройка VBA: AutoMacro.
AutoMacro также содержит много других Генераторы кода, обширный Библиотека кодаи мощный Инструменты кодирования.
Специальная вставка - Форматы и формулы
Помимо значений вставки, наиболее распространенными параметрами специальной вставки являются форматы вставки и формулы вставки.
Вставить форматы
Вставить форматы позволяет вставлять все форматирование ячеек.
12 | Диапазон ("A1: A10"). КопироватьДиапазон ("B1: B10"). PasteSpecial Paste: = xlPasteFormats |
Вставить формулы
Вставить формулы вставит только формулы ячеек. Это также чрезвычайно полезно, если вы хотите скопировать формулы ячеек, но не хотите копировать цвета фона ячеек (или другое форматирование ячеек).
12 | Диапазон ("A1: A10"). КопироватьДиапазон ("B1: B10"). PasteSpecial Paste: = xlPasteFormulas |
Вставить формулы и числовые форматы
Подобно вставке значений и числовых форматов выше, вы также можете копировать и вставлять числовые форматы вместе с формулами.
Здесь мы скопируем формулу ячейки только с форматированием учетных чисел и вставкой формул.
12 | Листы ("Лист1"). Диапазон ("D3"). КопироватьЛисты ("Лист2"). Диапазон ("D3"). Специальная вставка xlPasteFormulas |
Обратите внимание, как теряется форматирование чисел, и вместо этого отображается неокругленное значение.
Вместо этого давайте использовать форматы вставки формул и чисел:
12 | Листы ("Лист1"). Диапазон ("D3"). КопироватьЛисты ("Sheet2"). Range ("D3"). PasteSpecial xlPasteFormulasAndNumberFormats |
Теперь вы можете видеть, что форматирование чисел также вставлено с сохранением формата учета.
Специальная вставка - транспонировать и пропускать пробелы
Специальная вставка - транспонирование
Специальная вставка транспонирования позволяет копировать и вставлять ячейки, меняя ориентацию сверху-снизу на лево-право (или наоборот):
12 | Листы ("Лист1"). Диапазон ("A1: A5"). КопироватьЛисты ("Sheet1"). Range ("B1"). PasteSpecial Transpose: = True |
Специальная вставка - пропустить пробелы
Пропускать пробелы - это специальная опция вставки, которая, похоже, используется не так часто, как следовало бы. Это позволяет копировать только непустые ячейки при копировании и вставке. Таким образом, пустые ячейки не копируются.
В этом примере ниже. Мы скопируем столбец A, сделаем обычную вставку в столбец B и пропустим вставку пробелов в столбце C. Вы можете видеть, что пустые ячейки не были вставлены в столбец C на изображении ниже.
123 | Листы ("Лист1"). Диапазон ("A1: A5"). КопироватьЛисты ("Sheet1"). Range ("B1"). PasteSpecial SkipBlanks: = FalseЛисты ("Sheet1"). Диапазон ("C1"). PasteSpecial SkipBlanks: = True |
Другие специальные параметры вставки
Специальная вставка - Комментарии
1 | Листы ("Лист1"). Диапазон ("A1"). Копировать листы ("Лист1"). Диапазон ("E1"). PasteSpecial xlPasteComments |
Специальная вставка - проверка
12 | Листы ("Лист1"). Диапазон ("A1: A4"). КопироватьЛисты ("Sheet1"). Диапазон ("B1: B4"). PasteSpecial xlPasteValidation |
Специальная вставка - все с использованием исходной темы
123 | Книги (1). Таблицы ("Лист1"). Диапазон ("A1: A2"). КопироватьРабочие книги (2). Таблицы ("Sheet1"). Range ("A1"). PasteSpecialРабочие книги (2) .Sheets ("Sheet1"). Range ("B1"). PasteSpecial xlPasteAllUsingSourceTheme |
Специальная вставка - все, кроме границ
123 | Диапазон ("B2: C3"). КопироватьДиапазон ("E2"). PasteSpecialДиапазон ("H2"). PasteSpecial xlPasteAllExceptBorders |
PasteSpecial - Ширина столбца
Мой личный фаворит. PasteSpecial Column Widths скопирует и вставит ширину столбцов.
123 | Диапазон ("A1: A2"). КопироватьДиапазон ("C1"). PasteSpecialДиапазон ("E1"). PasteSpecial xlPasteColumnWidths |
PasteSpecial - Все условные форматы слияния
123 | Диапазон ("A1: A4"). КопироватьДиапазон ("C1"). PasteSpecialДиапазон ("E1"). PasteSpecial xlPasteAllMergingConditionalFormats |