Макрокопирование данных с листа на лист в Excel

Обновлено: 03.07.2024

Копирует лист в другое место в текущей книге или в новую книгу.

Синтаксис

выражение. Копировать (До, После)

выражение Переменная, представляющая объект Worksheet.

Параметры

Имя	Обязательный/Необязательный	Тип данных	Описание
До	Необязательно	Вариант	Лист, перед которым будет размещен скопированный лист. Вы не можете указать До, если вы укажете После.
После	Необязательно	Вариант	Лист, после которого будет размещен скопированный лист. Вы не можете указать После, если вы указали До.

Примечания

Если вы не укажете ни До, ни После, Microsoft Excel создаст новую книгу, содержащую скопированный объект Worksheet. Вновь созданная рабочая книга содержит свойство Application.ActiveWorkbook и содержит один рабочий лист. Один лист сохраняет свойства Name и CodeName исходного листа. Если скопированный рабочий лист содержит кодовый лист рабочего листа в проекте VBA, он также переносится в новую рабочую книгу.

Выбранный массив из нескольких рабочих листов можно скопировать в новый пустой объект Workbook аналогичным образом.

Источник и место назначения должны находиться в одном и том же экземпляре Excel.Application, в противном случае возникнет ошибка времени выполнения "1004": такой интерфейс не поддерживается, если используется что-то вроде Sheet1.Copy objWb.Sheets(1) или среда выполнения ошибка '1004': метод копирования класса Worksheet завершился неудачно, если использовалось что-то вроде ThisWorkbook.Worksheets("Sheet1").Copy objWb.Sheets(1).

Пример

В этом примере копируется Лист1, размещая копию после Листа3.

В этом примере лист Sheet1 сначала копируется в новую пустую книгу, а затем сохраняется и закрывается новая книга.

В этом примере листы Sheet1, Sheet2 и Sheet4 копируются в новую пустую книгу, а затем сохраняются и закрываются.

Поддержка и обратная связь

Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

Итог: узнайте, как использовать макросы VBA для копирования и вставки данных из одной книги Excel в другую, включая добавление данных в конец существующего диапазона или замену данных.

Уровень квалификации: средний

Загрузить файлы Excel

Следите за видео выше, используя те же файлы Excel, что и я. Вы можете скачать их, нажав ниже. Вот книга, из которой я копирую данные из в моем примере:

New-Data.xlsx (12,2 КБ)

А вот книга, в которую я копирую данные в. Это тот, в котором есть весь код макроса:

Reports.xlsm (22,0 КБ)

Копирование данных из одной книги в другую с помощью макросов Excel

Существует несколько способов копирования и вставки данных с помощью VBA. Сначала мы собираемся использовать метод Range.Copy. Это позволяет нам выполнить все действие в одной строке кода.

Метод Range.Copy имеет необязательный параметр Destination, который позволяет нам указать диапазон, в который мы хотим вставить данные.

Мы ссылаемся на исходную книгу, лист и диапазон, из которых хотим скопировать. Для параметра Destination мы ссылаемся на целевую книгу, рабочий лист и начальную ячейку диапазона для вставки.

Метод Range.Copy выполняет обычное копирование и вставку, включая форматирование и формулы. Если вы просто хотите вставить значения, ниже приведен пример.

Важные моменты, которые следует помнить

При использовании этого макроса для копирования данных из одной книги в другую помните об этом.

Вы должны указать правильное расширение файла в свойстве Workbooks (подробности см. в видео выше).
Для этого в рабочих книгах не обязательно должны быть включены макросы.
При желании этот код можно сохранить в отдельной книге, например в личной книге макросов. (Узнайте, как создать личную книгу макросов, здесь.)
Вам не нужно сначала выбирать или активировать книги, листы или даже диапазоны.Это связано с тем, что в коде уже указаны эти данные.
При использовании этого кода должны быть открыты обе книги. Но процесс открытия и закрытия книг можно автоматизировать с помощью дополнительного кода:

Специальный метод PasteSpecial для вставки значений, форматов и т. д.

При вставке данных в целевую книгу с помощью VBA вы также можете использовать любые обычные функции специальной вставки.

Ниже приведен пример макроса. Вы заметите, что в моем примере используется тип PasteValues, но вы также можете использовать PasteFormulas, PasteFormats или любые другие доступные параметры PasteSpecial. Вот список PasteTypes.

Чтобы узнать больше о параметрах PasteSpecial, посмотрите мою серию видео о копировании и вставке с помощью VBA.

Вставка ниже последней ячейки

Иногда размер диапазонов данных в исходном и целевом файлах будет меняться при каждом запуске макроса. Например, у вас может быть ежедневная задача по добавлению новых записей из экспортированного листа в основной список в другой книге.

В этом случае вам нужно добавить новые записи непосредственно под последней записью на листе назначения. Для этого вы можете использовать следующий макрос.

Этот код вставляет ваши исходные данные сразу после существующих данных конечного листа.

Помимо поиска последней строки в диапазоне или на листе, вы также можете найти последний столбец или ячейку. Посмотрите мой пост и видео о трех способах поиска последней использованной строки или столбца, чтобы узнать больше.

Очистка диапазона назначения перед вставкой

Вместо добавления в список диапазона назначения вы можете очистить существующий диапазон перед вставкой новых данных. Вы можете сделать это с помощью этого макроса.

Запуск этого макроса удалит все существующие данные в целевом диапазоне перед вставкой данных из исходного листа.

Альтернативный код для копирования данных в текущую книгу

Я также хотел представить вам немного другой вариант вашего макроса. Вместо того, чтобы идентифицировать рабочую книгу назначения по имени, вы можете использовать свойство ThisWorkbook. Это можно сделать, если макрос хранится в целевой (или исходной) книге.

Таким образом, вам не придется менять код, если вы измените имя файла для целевой книги. Вот код VBA, использующий ThisWorkbook.

Это напоминает мне, что VBA всегда будет предполагать, что макрос, который вы хотите запустить, относится к активной книге, если вы не укажете книгу в каждой строке кода. Я рассказываю об этом критическом допущении и других важных аспектах выполнения кода VBA в этом видео о допущениях VBA.

Копировать и вставлять между листами в одной книге

Вы можете изменить любой из приведенных выше примеров для копирования и вставки между листами в одной книге. Просто используйте одну и ту же ссылку на рабочую книгу для диапазонов копирования и назначения. Вот пример.

Не всегда нужно указывать рабочую книгу, но это хорошая привычка. В противном случае VBA делает предположения, из-за которых у вас могут возникнуть проблемы.

Заключение

Я надеюсь, что эти советы и макросы помогут вам сэкономить время при копировании данных между книгами. Автоматизация этой утомительной задачи поможет предотвратить ошибки и упростить обновление отчетов другими пользователями.

Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо! 🙂

Копирование данных с помощью VBA — это распространенный процесс, который решается с помощью хорошо написанного кода. Допустим, мы хотим регулярно копировать данные с Sheet1 на Sheet2 с помощью кода.

Следующая процедура Excel VBA копирует данные с активного листа и вставляет их в первую пустую ячейку в нижней части диапазона на другом листе.

Красный (2) в конце — это часть кода, которая смещается к первой пустой строке в столбце A.

Если мы хотим включить начальный лист в процедуру копирования, есть еще один способ скопировать данные с одного листа на другой.

В приведенном выше примере Лист1 — это кодовое имя рабочего листа для копируемого начального листа, а Лист2 — это кодовое имя листа, на который мы копируем.

Кодовое название листа

Кодовое имя листа — это кодовое имя копируемого листа. Он используется не так интенсивно, как должен был бы, но в этом его преимущество: если имя вашего рабочего листа изменено, код не сломается. В примере слева Лист1 — это кодовое имя листа с именем Данные, а Лист2 — это кодовое имя листа с именем Консолидация. Если любое имя листа изменено при условии, что используется кодовое имя рабочего листа, код останется стабильным.Всегда используйте кодовое имя рабочего листа в VBA.

Другие методы копирования с помощью VBA

В VBA есть множество способов делать одни и те же действия, и следующий пример будет повторять описанный выше. Это выглядит менее эффективно, однако должно работать быстрее, чем копирование данных для больших наборов данных.

Sub UseAnArray() 'Excel VBA для копирования данных
Dim ar As Variant

Подробнее об использовании вариантов массива можно узнать в статье Массив VBA.

Следующий метод полезен для диапазонов, которые не являются непрерывными, но имеют одинаковый размер. Под одинаковым размером я подразумеваю одинаковую глубину. Изучите следующий пример:

В приведенном выше примере столбец B будет пропущен, а данные из A1 в Ac и из C1 в D3 будут вставлены в диапазон листа 2 A1. Хитрость при копировании таких данных заключается в том, чтобы размер диапазона был одинаковым для каждого сегмента. [A1:A3, C1:C3] оба диапазона заканчиваются строкой 3. Это можно расширить, чтобы добавить больше диапазонов. Например:

Как видите, диапазон Excel может увеличиваться при условии, что диапазон имеет одинаковый размер. Строки с 1 по 3 повторяются выше, и хотя это верно, вы можете продолжать помещать дополнительные диапазоны в квадратные скобки Excel.

Иногда заголовки будут меняться от периода к периоду на основе загруженного набора данных. В этом случае вы можете включить процедуру поиска, чтобы уловить местоположение для определенных слов. Следующее найдет расположение Header1.

Option Explicit
Sub FindthenCopy() 'Excel VBA находит положение заголовка
Dim fn As Long

fn=Rows("1:1").Find("Заголовок 1").Column
Range(Cells(2, fn), Cells(Rows.Count, fn).End(xlUp)) .Copy Sheet4.Range("A65536").End(xlUp)(2)

После того, как заголовок 1 будет найден, процедура VBA скопирует данные из найденного столбца в первую пустую строку в столбце A листа2.

Копирование данных, соответствующих критериям

Пользователи Excel прагматичны и выросли, используя оператор IF в повседневном использовании Excel. Поэтому, когда дело доходит до VBA, естественно предположить, что лучший способ переместить данные из одного места в другое — это проверить каждую ячейку, чтобы увидеть, соответствует ли она условию, а затем переместить всю строку. Ниже приводится часто встречающийся код, который я вижу на форумах Excel;

Опция Explicit
Sub Test()
Dim i As Integer

Range("B" & i).EntireRow.Copy
Sheets("Sheet2").Select
Range("A1").End(xlDown).Offset(1, 0). Выберите
Selection.PasteSpecial xlPasteValues
Листы ("Лист1"). Выберите

Возможно, я вставил слишком много строк, но предположил, что рекордер использовался для генерации большей части кода. Проблема с приведенным выше VBA заключается в том, что по мере экспоненциального роста списка информации время, необходимое для запуска процесса, уменьшается в зависимости от размера набора данных. Если список состоит из 100 строк, Excel должен выполнить 100 действий, если все элементы соответствуют критериям. Можно утверждать, что в приведенном выше примере Excel должен сделать 200 действий, задать вопрос 100 раз, потенциально выполнить действие еще 100 раз.

Простое решение

Легче задать один вопрос Excel и выполнить одно действие Excel для всего набора данных. Это можно сделать, опираясь на предыдущие уроки, описанные в разделе сайта Autofiler with VBA. В приведенном выше примере очень простым способом перемещения данных будет следующая конструкция VBA:

Range("A1:A101").AutoFilter 1, "Ford"
Range("A1:A101").Copy Sheet2.Range("A" & Rows.Count).End(xlUp)( 2)
Range("A1").Autofilter 'Выкл с автофильтром

Этот метод удобен, если вы хотите регулярно добавлять информацию в сводную таблицу. VBA изолирует подобные данные и легко перемещает информацию между листами.

Вставьте строку внизу диапазона и скопируйте формулу сверху

Если у вас есть новые данные, добавленные в нижнюю часть диапазона ячеек, и вам нужно скопировать формулу из ячеек выше, тогда следующий код Excel VBA устранит эту ситуацию. Он копирует последнюю строку данных, вставляет новую строку, а формулы сверху перемещаются на одну строку вниз в идеальной последовательности.

Rows(Range("A" & Rows.Count).End(xlUp).Row).Copy
Rows(Range("A" & Rows.Count).End(xlUp).Row + 1 ).Вставить xlDown

Эта концепция раскрывается далее в следующей статье, где вы указываете в ячейках, сколько раз следует копировать указанную выше строку. Скопируйте n строк в Excel

< бр />

Копирование данных с одного рабочего листа на другой — довольно частый и рутинный сценарий. К счастью, у нас есть способ скопировать данные листа VBA из одного в другой с помощью макроса Excel VBA. Более того, у нас есть много способов решить эту предположительно простую проблему.

Копирование VBA с использованием функции копирования диапазона

Простым способом копирования данных между рабочими листами является использование функции копирования диапазона VBA. Мы можем сделать это разными способами

Ниже описаны простые шаги, описанные выше:

Объявите и определите исходный и конечный листы («Src» и «Dst»)
Используйте функцию копирования диапазона, чтобы скопировать определенный диапазон из исходного листа в целевой.

Это вид рабочего листа до примера:

Это вид после использования функции копирования диапазона VBA:

Копировать VBA только на UseRanged

Что же делать, если вы не хотите заморачиваться с проверкой используемого диапазона исходного листа и просто хотите скопировать его целиком? Вы можете использовать атрибут UsedRange исходного рабочего листа — см. пример ниже.

В чем разница? В приведенном выше фрагменте кода вместо копирования определенного диапазона мы копируем весь используемый диапазон исходного рабочего листа. Используемый диапазон всегда представляет собой любой прямоугольный диапазон ячеек, который был каким-либо образом изменен на рабочем листе, т. е. отредактирован, изменен цвет, граница и т. д. Используемый диапазон всегда имеет начальную и конечную ячейки, то есть ближайшую и самую дальнюю от A1, которые были изменены. В моем случае результат был таким же, но код выше намного удобнее.

VBA Копировать и вставлять только значения

Функция VBA Copy Range скопирует все содержимое ячейки, включая форматирование, границы, условное форматирование и т. д. Однако иногда требуется скопировать различную выбранную информацию или только значения ячейки без форматирования. Для этого нам нужно изменить макрос выше:

На этот раз мы изменили последние две строки предыдущих процедур. Сначала мы копируем выбранный диапазон из исходного рабочего листа. Затем мы используем функцию VBA Range PasteSpecial, чтобы вставить диапазон из буфера обмена только со значениями на наш рабочий лист назначения. Вот эффект:

Выводы

Копировать данные листа VBA просто и часто используется. Также ознакомьтесь с моей надстройкой SQL, чтобы узнать, как использовать SQL для копирования данных для одного или нескольких рабочих листов.

Читайте также: