Макрос в excel для переноса значений с одного листа на другой

Обновлено: 21.11.2024

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

Мы кратко рассмотрим один простой пример использования VBA в Excel для извлечения значений из одного листа , но это лишь верхушка айсберга того, что Excel и VBA могут делать вместе.

Доступ к редактору Visual Basic

Для начала вам нужно найти редактор Visual Basic в Excel. Поиск этого зависит от того, какую версию Excel вы используете, но для большинства современных версий редактор Visual Basic можно найти на вкладке «Разработчик» на ленте меню.

Найдя его, просто нажмите «Макросы» , введите имя макроса (в этом примере мы будем использовать MySum), затем нажмите «Создать», чтобы открыть редактор и начать создание сценария.

Сценарий

Оказавшись в редакторе, вы увидите объявление макроса MySum, который представляет собой стандартную функцию vba, которую мы можем редактировать в соответствии с нашими требованиями.

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

Таким образом, чтобы начать наш скрипт, нам нужно выбрать соответствующий рабочий лист с именем Sales , а затем, используя этот выбранный объект , сузить наш выбор, захватив определенные ячейки (известные как диапазон ячеек), которые мы хотим суммировать.

Теперь нам действительно нужно получить общее количество или сумму всех этих значений, поэтому мы заключаем предыдущий вызов объекта в WorksheetFunction of Sum, например так:

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

Для этого мы будем использовать объект ActiveCell, установив его значение равным нашей сумме:

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

Запуск макроса

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

Чтобы запустить сохраненный макрос, просто вернитесь на вкладку "Разработчик", снова выберите "Макросы" и выберите свой макрос MySum. Содержимое ячейки теперь должно быть заменено суммой указанных нами значений столбца Продажи B.

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

Введение

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

Пример

Первый шаг – принять решение по следующим вопросам:

  1. Каков диапазон данных для копирования, т. е. ячейки от A1 до B20.
  2. Какое имя листа содержит диапазон копируемых данных, например «ImportSheet» или «Sheet2».
  3. Какое имя рабочего листа, куда будут скопированы данные.
  4. Какое начальное местоположение (ячейка) будет скопировано, например ячейка A1?

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

Время VBA

Следующий шаг — открыть окно VBA в Excel, чтобы можно было написать сценарий. Чтобы открыть окно VBA, выберите вкладку «Разработчик» на ленте Excel, а затем щелкните значок Visual Basic в крайнем левом углу. Альтернативно, сочетание клавиш ALT + F11 откроет окно VBA.

Открыв окно VBA, следующим шагом будет вставка нового модуля, где будет написан скрипт VBA. Для этого нажмите «Вставить» > «Модуль» в верхней строке меню:

Откроется окно кода (module1), в котором можно написать сценарий VBA:

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

Код VBA

Код VBA для копирования данных из таблицы ImportSheet на лист DataTable выглядит следующим образом:

Построчный разбор этого кода VBA:

  • Sub CopyData() — всем сценариям VBA требуется имя, Sub сообщает Excel, что это подпроцедура, а CopyData — это имя этой подпроцедуры, поэтому его можно изменить на другое имя. Квадратные скобки должны быть включены в конце этой строки кода.
  • Worksheets("ImportSheet").Range("A1:C13").Copy — эта строка кода сообщает Excel, какие данные копировать. В этом примере мы хотим, чтобы данные на рабочем листе с именем ImportSheet в диапазоне от A1 до C13 были скопированы.
  • Worksheets("DataTable").Range("A1").PasteSpecial — эта строка кода сообщает Excel, куда копировать данные. В этом примере данные должны быть скопированы на рабочий лист DataTable, начиная с ячейки A1.
  • End Sub — сообщает Excel, что подпроцедура завершена и должна быть включена.

Чтобы запустить код VBA, нажмите значок кнопки воспроизведения в окне VBA, либо нажмите клавишу F5, чтобы запустить код.

Теперь данные скопированы из таблицы ImportSheet в таблицу DataTable:

Понимание VBA является ключом к тому, чтобы стать опытным пользователем Excel, а также отличным способом сделать карьеру в области анализа данных. Большинство компаний так или иначе используют Excel, но мало кто понимает VBA. На этом сайте есть и другие публикации по VBA, которые помогут вам в этом, но для тех, кому нужен справочник, мой фаворит — VBA и макросы, написанные гуру Excel Биллом Джеленом и Трейси Сирстад. Вы можете проверить копию на Amazon, нажав на изображение ниже.

Итог: узнайте, как использовать макросы 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

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