Скопировать только значения excel vba
Подобно рабочему листу, когда мы копируем данные и вставляем их в другой диапазон ячеек, у нас есть специальный метод вставки, который позволяет нам вставлять данные как сами по себе, или только формулы, или только значения, и таким же образом мы можем использовать Специальную вставку в VBA, используя метод свойства диапазона следующим образом: диапазон. вставьте special(), указав нужный тип в скобках.
Специальная вставка в VBA
Вставить специальный в excel Специальная вставка в Excel Специальная вставка в Excel позволяет вставлять частичные аспекты скопированных данных. Существует несколько способов специальной вставки в Excel, в том числе щелчок правой кнопкой мыши по целевой ячейке и выбор специальной вставки или использование сочетания клавиш, например CTRL+ALT+V или ALT+E+S. читать далее служит во многих отношениях в нашей повседневной работе. Используя специальную пасту, мы можем делать гораздо больше вещей, чем обычные. Копировать и вставлять можно везде в компьютерном мире. Но специальная вставка — это продвинутая вещь в Excel.
Как и обычная вставка Excel, специальная в VBA, у нас есть специальный метод вставки для вставки скопированных данных. Копирование вещей в Excel не является чем-то странным для пользователей Excel, они копируют, вставляют и в большинстве случаев используют специальную вставку для различных целей.
В обычном Excel вставка включает множество параметров, таких как вставка только значений, вставка формул, вставка форматов и т. д.…
Вставить специальные должны Вставка, операция, пропуск пробелов и транспонирование как это и в VBA. У нас есть все параметры с Специальная вставка метод.
Формула специальной вставки в VBA
Ниже приведена формула для специальной вставки в VBA.
Специальная вставка доступна с Объект диапазона VBA Объект диапазона VBA Диапазон — это свойство в VBA, которое помогает указать конкретную ячейку, диапазон ячеек, строку, столбец или трехмерный диапазон. В контексте рабочего листа Excel объект диапазона VBA включает одну или несколько ячеек, распределенных по различным строкам и столбцам. читать далее потому что после копирования данных мы будем вставлять их в диапазон ячеек, поэтому доступен специальный метод вставки с диапазон объект.
Тип пасты: После копирования данных, как вы хотите вставить. Если вы хотите вставить значения, формулы, форматы, проверку и т. д. Ниже приведен полный список параметров, доступных в разделе Тип вставки.
Вставить специальную операцию: Во время вставки вы хотите выполнять какие-либо операции, такие как сложение, вычитание, деление, умножение или ничего.
Примеры специальной вставки в Excel VBA
Ниже приведены примеры специальной вставки в VBA.
Пример № 1. Вставка только значений с помощью функции VBA PasteSpecial
В первом примере мы будем выполнять вставку только значений с помощью специальной вставки. Предположим, что ниже приведены данные, которые у вас есть в имени листа под названием «Данные о продажах».
Теперь мы выполним задачу копирования и вставки, используя несколько специальных методов вставки. Выполните следующие шаги.
Шаг 1: Сначала создайте имя макроса.
Шаг 2: Сначала скопируйте диапазон от A1 до D14 с имени листа «Данные о продажах». Чтобы скопировать диапазон, примените приведенный ниже код.
Код:
Шаг 3: После копирования данных мы будем вставлять значения от G1 до J14. Во-первых, укажите диапазон.
Код:
Шаг 4: После выбора диапазона нам нужно вставить. Поэтому поставьте точку (.) и выберите специальный метод вставки.
Код:
Шаг 5: Из выпадающего списка выберите вариант «кслпастевалуес».
Код:
Шаг 6: Теперь запустите этот код с помощью клавиши F5 или вручную и посмотрите, что произойдет.
Итак, наш код скопировал данные из A1 в D14 и вставил из G1 в J14 в качестве значений.
Оно выполнило задачу горячая клавиша эксель Клавиша быстрого доступа Excel Ярлык Excel — это способ более быстрого выполнения ручного задания. читать далее на листе ALT+Е+S+V.
Пример № 2 — Вставить все с помощью VBA PasteSpecial
Теперь посмотрим, что произойдет, если мы выполним задачу xlPasteAll.
Код:
Теперь, если вы запустите этот код вручную через опцию запуска или нажав клавишу F5, у нас будут как есть данные.
Пример № 3. Вставка форматов с использованием функции VBA PasteSpecial
Теперь мы увидим, как вставлять только форматы. Приведенный ниже код сделает эту работу за нас.
Код:
Если запустить этот код с помощью клавиши F5 или вручную, то мы получим только формат копируемого диапазона, больше ничего.
Пример № 4. Вставка ширины столбца с помощью специальной вставки VBA
Теперь мы увидим, как вставить только ширина колонки Ширина колонки Пользователь может установить ширину столбца на листе Excel от 0 до 255, где ширина одного символа равна одной единице. Ширина столбца для нового листа Excel составляет 8,43 символа, что равно 64 пикселям. читать далее из скопированного диапазона. Для этого я увеличил ширину столбца для одного из моих столбцов данных.
Примените приведенный ниже код, он вставит только ширину столбца скопированного диапазона.
Код:
Запустите этот код и посмотрите разницу в ширине столбца.
Теперь мы видим, что ширина столбца Sales была увеличена до ширины столбца нашего скопированного столбца диапазона.
Пример № 5. Скопируйте данные с одного листа на другой лист с помощью специальной опции VBA Paste.
Мы видели, как копировать и вставлять данные на один и тот же лист. Теперь мы будем, как вставить с одного листа на другой лист.
Шаг 1: Прежде чем мы выберем диапазон, нам нужно сказать, из какого листа нам нужно выбрать данные.
Код:
Шаг 2: После выбора листа по его имени нам нужно выбрать диапазон на этом листе. Скопируйте его.
Код:
В приведенном выше коде указано, что в названии листа «Данные о продажах» скопируйте диапазон («A1: D14»)
Шаг 3: Поскольку мы вставляем его на другой лист, нам нужно выбрать лист по его имени.
Код:
Шаг 4: Теперь на листе «Лист месяца» выберите диапазон.
Код:
Шаг 5: Используя специальную вставку, мы будем вставлять значения и формат.
Код:
Шаг 6: Мы не только вставляем значения и формат с помощью VBA Paste Special, но также вставляем его как TRANSPOSE.
Код:
Теперь запустите этот код. Он скопирует и перенесет данные в «Лист месяца».
Иногда на то, чтобы придумать некоторые вещи, уходит очень много времени. Но когда их УЖЕ придумали, то постфактум они кажутся очевидными и даже банальными. Из серии "а что, так можно было?".
С самых первых версий в строке состояния внизу окна Microsoft Excel традиционно отображались итоги по выделенным ячейкам:
При желании, можно было даже щёлкнуть по этим итогам правой кнопкой мыши и выбрать в контекстном меню, какие именно функции мы хотим видеть:
И только совсем недавно в последних обновлениях Excel разработчики Microsoft добавили простую, но гениальную фишку - теперь при щелчке мышью по этим итогам они копируются в буфер!
Но что делать тем, у кого пока (или уже?) нет такой версии Excel? Тут могут помочь несложные макросы.
Копирование суммы выделенных ячеек в Буфер с помощью макроса
Откройте на вкладке Разработчик (Developer) редактор Visual Basic или воспользуйтесь для этого сочетанием клавиш Alt + F11 . Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда следующий код:
Логика его работы проста:
- Сначала идёт "защита от дурака" - мы проверяем что именно выделено. Если выделены не ячейки (а, например, диаграмма), то выходим из макроса.
- Затем при помощи команды GetObject мы создаем новый объект данных, где будет храниться впоследствии наша сумма выделенных ячеек. Длинный и непонятный буквенно-цифровой код - это, на самом деле, ссылка на ветку реестра Windows, где лежит библиотека Microsoft Forms 2.0 Object Library, которая умеет создавать такие объекты. Иногда такой трюк ещё называют неявным поздним связыванием. Если его не использовать, то пришлось бы заранее делать в файле ссылку на эту библиотеку через меню Tools - References.
- Сумма выделенных ячеек считается командой WorksheetFunction.Sum(Selection), а затем полученная сумма помещается в буфер обмена командой PutInClipboard
Для удобства использования можно, конечно же, повесить этот макрос на сочетание клавиш с помощью кнопки Макросы на вкладке Разработчик (Developer - Macros) .
А если хочется видеть, что именно скопировалось после выполнения макроса, то можно включить панель Буфер обмена с помощью маленькой стрелки в правом нижнем углу соответствующей группы на Главной (Home) вкладке:
Не только сумма
Если кроме банальной суммы хочется что-то ещё, то можно воспользоваться любой из функций, которую нам предоставляет объект WorksheetFunction:
Например, там есть:
- Sum - сумма
- Average - среднее арифметическое
- Count - количество ячеек с числами
- CountA - количество заполненных ячеек
- CountBlank - количество пустых ячеек
- Min - минимальное значение
- Max - максимальное значение
- Median - медиана (центральное значение)
- . и т.д.
С учетом фильтров и скрытых строк-столбцов
Что если в выделенном диапазоне окажутся скрытые (вручную или фильтром) строки или столбцы? Чтобы не учитывать их в итогах, нужно будет чуть-чуть модифицировать наш код, добавив к объекту Selection свойство SpecialCells(xlCellTypeVisible):
В этом случае подсчет любой функции итога будет применён только к видимым ячейкам.
Если нужна живая формула
Если пофантазировать, то можно придумать сценарии, когда в буфер лучше скопировать не число (константу), а именно живую формулу, которая подсчитывает нужные нам итоги по выделенным ячейкам. В этом случае придётся склеить формулу из фрагментов, добавив к ней дополнительно удаление знаков доллара и замену запятой (которая используется как разделитель адресов нескольких выделенных диапазонов в VBA) на точку с запятой:
Суммирование с дополнительными условиями
Ну и, наконец, для совсем уже маньяков можно написать макрос, который будет суммировать не все выделенные ячейки, а только те, что удовлетворяют заданным условиям. Так, например, будет выглядеть макрос помещающий в Буфер сумму выделенных ячеек, если их значения больше 5 и при этом они залиты любым цветом:
Как легко сообразить, условия можно задать абсолютно любые - вплоть до форматов ячеек - и в любых количествах (в том числе, связывая их между собой логическими операторами or или and). Простор для фантазии большой.
Копирует содержимое объекта ADO или DAO Recordset на таблицу, начиная с верхнего левого угла указанного диапазона. Если объект Recordset содержит поля с объектами OLE в них, этот метод не удается.
Синтаксис
выражения. CopyFromRecordset (Data, MaxRows, MaxColumns)
выражение: переменная, представляющая объект Range.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Data | Обязательный | Variant | Объект Recordset для копирования в диапазон. |
MaxRows | Необязательный | Variant | Максимальное количество записей для копирования на таблицу. Если этот аргумент опущен, все записи в объекте Recordset копируется. |
MaxColumns | Необязательный | Variant | Максимальное количество полей для копирования на таблицу. Если этот аргумент опущен, все поля в объекте Recordset копируется. |
Возвращаемое значение
Примечания
Копирование начинается в текущем ряду объекта Recordset . После завершения копирования свойство EOF объекта Recordset является True.
Пример
В этом примере имена полей из объекта Recordset DAO копируется в первом ряду таблицы и форматирует имена как смелые. Затем в примере копируется набор записей на таблицу, начиная с ячейки A2.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Range.Cut – это метод, который вырезает объект Range (диапазон ячеек) в буфер обмена или перемещает его в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен (перемещен) вырезанный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект вырезается в буфер обмена. |
Для вставки на рабочий лист диапазона ячеек, вырезанного в буфер обмена методом Range.Cut, следует использовать метод Worksheet.Paste.
Метод Range.Copy
Range.Copy – это метод, который копирует объект Range (диапазон ячеек) в буфер обмена или в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен скопированный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект копируется в буфер обмена. |
Метод Worksheet.Paste
Синтаксис
Метод Worksheet.Paste работает как с диапазонами ячеек, вырезанными в буфер обмена методом Range.Cut, так и скопированными в буфер обмена методом Range.Copy.
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон (ячейка), указывающий место вставки содержимого буфера обмена. Если этот параметр не указан, используется текущий выделенный объект. |
Link | Необязательный параметр. Булево значение, которое указывает, устанавливать ли ссылку на источник вставленных данных: True – устанавливать, False – не устанавливать (значение по умолчанию). |
В выражении с методом Worksheet.Paste можно указать только один из параметров: или Destination, или Link.
Для вставки из буфера обмена отдельных компонентов скопированных ячеек (значения, форматы, примечания и т.д.), а также для проведения транспонирования и вычислений, используйте метод Range.PasteSpecial (специальная вставка).
Примеры
Вырезание и вставка диапазона одной строкой (перемещение):
Вырезание ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование и вставка диапазона одной строкой:
Копирование ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование одной ячейки и вставка ее данных во все ячейки заданного диапазона:
18 комментариев для “VBA Excel. Вырезание, копирование и вставка ячеек (диапазонов)”
Странно, что не рассмотрено копирование ячеек, которые Cells.
Например, следующая строка копирует ячейку A1 в B2
а эта делает тоже самое, но демонстрирует,
как можно добавить размер и смещение:
Cells ( 1 , "A" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 ) . Copy Cells ( 2 , "B" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 )
Здравствуйте!
Скажите, как можно копировать на Лист 1, а вставить на Лист 2 диапазон ячеек целиком, либо отсортированный по 1 признаку диапазон(например по какому-то определенному значению, скажем, числу «500» в одном столбце Листа 1)?
Привет, Максим!
Вот пример сортировки таблицы по значению 500 в первом столбце на "Лист6" и копирования диапазона с "Лист6" на "Лист4" :
Спасибо большое, только я, наверно, неправильно описал ситуацию.
Сперва из эталонной таблицы Лист 1 копируем весь список на существующий Лист4.
Затем, пропускаем шапку, и, начиная со строки7, уже на Лист4 ищем строку, у которой в столбце 5 стоит число 500.
Вырезаем эту строку и вставляем на позицию строки 7. т.е первая после шапки.
Ищем следующую строку — вырезаем, потом вставляем на позицию строки 7+1=8 и т.д.
В итоге оставим только те строки, у которых в столбце 5 стоит число «500», остальные удаляются строки.
(пробовал менять местами строки в предыдущем коде, ошибок нет, копирует, переходит на лист 4, но строки на нем не удаляет, циклы пошагово проходят, но ничего не изменяется.)
Range.PasteSpecial (специальная вставка) – это метод, который вставляет диапазон ячеек, скопированный в буфер обмена, из буфера обмена в указанное место на рабочем листе с учетом заданных параметров специальной вставки.
Синтаксис
Специальная вставка работает только с данными ячеек, скопированными в буфер обмена методом Range.Copy. При попытке применить метод Range.PasteSpecial к ячейкам, вырезанным в буфер обмена методом Range.Cut, возникает ошибка.
Параметры специальной вставки
Список параметров метода Range.PasteSpecial:
Параметры | Описание |
---|---|
Paste | Необязательный параметр. Константа из коллекции XlPasteType, указывающая на часть данных вставляемого диапазона, которую следует вставить. По умолчанию вставляются все данные. |
Operation | Необязательный параметр. Константа из коллекции XlPasteSpecialOperation, указывающая на математические операции, которые следует провести со скопированными данными и данными в ячейках назначения. По умолчанию вычисления не производятся. |
SkipBlanks | Необязательный параметр. Булево значение, которое указывает, вставлять ли в конечный диапазон пустые ячейки: True – не вставлять, False – вставлять (значение по умолчанию). |
Transpose | Необязательный параметр. Булево значение, которое указывает, следует ли транспонировать строки и столбцы при вставке диапазона: True – транспонировать, False – не транспонировать (значение по умолчанию). |
Смотрите другой способ транспонировать диапазоны ячеек и двумерные массивы.
Константы XlPasteType
Список констант из коллекции XlPasteType, которые могут быть использованы в качестве аргумента параметра Paste:
Константа | Значение | Описание |
---|---|---|
xlPasteAll | -4104 | Вставка всех данных (по умолчанию). |
xlPasteAllExceptBorders | 7 | Вставка всех данных, кроме границ. |
xlPasteAllMergingConditionalFormats | 14 | Вставка всех данных со слиянием условных форматов исходного и нового диапазонов. |
xlPasteAllUsingSourceTheme | 13 | Вставка всех данных с использованием исходной темы. |
xlPasteColumnWidths | 8 | Вставка ширины столбцов. |
xlPasteComments | -4144 | Вставка комментариев. |
xlPasteFormats | -4122 | Вставка форматов исходного диапазона. |
xlPasteFormulas | -4123 | Вставка формул. |
xlPasteFormulasAndNumberFormats | 11 | Вставка формул и форматов чисел. |
xlPasteValidation | 6 | Вставка правил проверки данных из ячеек исходного диапазона в новый диапазон. |
xlPasteValues | -4163 | Вставка значений. |
xlPasteValuesAndNumberFormats | 12 | Вставка значений и форматов чисел. |
Константы XlPasteSpecialOperation
Список констант из коллекции XlPasteSpecialOperation, которые могут быть использованы в качестве аргумента параметра Operation:
Константа | Значение | Описание |
---|---|---|
xlPasteSpecialOperationAdd | 2 | Скопированные данные будут добавлены к значениям в ячейках назначения. |
xlPasteSpecialOperationDivide | 5 | Скопированные данные разделят значения в ячейках назначения. |
xlPasteSpecialOperationMultiply | 4 | Скопированные данные будут перемножены со значениями в ячейках назначения. |
xlPasteSpecialOperationNone | -4142 | Вычисления не выполняются при вставке данных (по умолчанию). |
xlPasteSpecialOperationSubtract | 3 | Скопированные данные будут вычтены из значений в ячейках назначения. |
Примеры
Примеры копирования и специальной вставки актуальны для диапазона "A1:B8" активного листа, ячейки которого заполнены числами:
Читайте также: