Vba excel перенести данные с одного листа на другой

Обновлено: 23.01.2025

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Импортируйте данные из другого листа с помощью функции Connections

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

1. Перейти к щелчку Данные > Беспроводные сети, см. снимок экрана:

2. В Подключения к книге диалоговое окно, нажмите Добавить кнопку, а в выскочившей Существующие подключения диалоговое окно, нажмите Найдите больше кнопку см. скриншоты:

3. Затем выберите книгу, данные которой вы хотите экспортировать на текущий лист.

4. После выбора книги нажмите Откройте и выберите один лист, который хотите использовать. Смотрите скриншот:

5. Затем нажмите OK, он вернется в Подключения к книге диалоговое окно, и выбранная вами книга добавлена в список, закройте это диалоговое окно.

6. Затем продолжайте нажимать Данные > Существующие подключения, см. снимок экрана:

7. В Существующие подключения в диалоговом окне щелкните книгу, которую вы только что добавили, и щелкните Откройте кнопка. Смотрите скриншот:

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

9. Затем нажмите OK, данные из другого листа были импортированы на указанный лист.

Ноты:

1. Используя этот метод, вы можете импортировать данные с типом таблицу, Отчет о сводной таблице or Сводная диаграмма и Отчет о сводной таблице.

2. Ваша рабочая книга была подключена к этим внешним данным, если вы хотите получить последние данные в вашей собственной книге. Идти к Данные > Обновить все чтобы получить обновленные данные.

Импортировать данные из другого листа с кодом VBA

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

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: импорт данных с другого листа

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

4. Выбрав книгу, нажмите Откройте и выберите лист или диапазон указанной книги, данные из которых необходимо экспортировать. Смотрите скриншот:

5. Затем нажмите OKи выберите одну ячейку, чтобы поместить импортированные данные на текущий лист, см. снимок экрана:

6. Продолжайте нажимать OK, и выбранные данные были импортированы в ваш рабочий лист.

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

Импортируйте данные с другого листа с помощью Kutools for Excel

Kutools for Excel также обеспечивает Вставить файл в курсор функция для вас, чтобы решить эту проблему.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас

1. Откройте книгу, в которую вы хотите вставить данные другого листа.

2. Нажмите Предприятие > Импорт / Экспорт > Вставить файл в курсор, см. снимок экрана:

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

4. Затем нажмите OK, и выберите один лист, если ваша книга содержит несколько листов из диалогового окна Выбор рабочего листа, см. снимок экрана:

5, Затем нажмите OK, выбранный вами рабочий лист был импортирован в ваш конкретный рабочий лист.

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

На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:

Исходная таблица задания №1

Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):

Часть результирующего списка задания №1

Решение копированием с листа на лист

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

Dim n1 As Long , n2 As Long , n3 As Long , n4 As Long , _ n1 = Sheets ( "Лист1" ) . Cells ( 1 , 1 ) . CurrentRegion . Rows . Count

Переменные:

n1 – количество строк в исходной таблице;
n2 – номер столбца текущей ячейки исходной таблицы, к которой обращается цикл;
n3 – номер строки текущей ячейки на втором листе;
n4 – номер столбца текущей ячейки на втором листе;
i1 – счетчик цикла For… Next;
gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.

Решение с использованием массивов

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

Подпрограммы Kopirovanie и Vstavka используются в цикле For. Next процедуры Resheniye2 по два раза, поэтому их коды вынесены за пределы процедуры Resheniye2 и вызываются по мере необходимости.

Переменные:

massiv1 – его элементам присваиваются значения ячеек исходной таблицы;
massiv2 – одномерный массив, заполняемый данными из переменной txt1;
massiv3 – двумерный массив, заполняемый данными из одномерного массива massiv2 и используемый для вставки очередной строки на второй лист;
txt1 – сюда копируются через разделитель значения элементов массива massiv1, предназначенные для заполнения очередной строки на втором листе;
n1 – количество строк в исходной таблице;
n2 – количество столбцов в исходной таблице;
n3 – номер текущей строки на втором листе;
n4 – количество столбцов текущей строки на втором листе (соответствует количеству элементов массива massiv2);
i1, i2, i3 – счетчики цикла For… Next;
gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.

Переменные, использующиеся более чем в одной процедуре, объявлены как глобальные в разделе Declarations программного модуля.

Visual Basic для приложений (VBA) - это идеальный способ настроить Excel 2010 в соответствии с вашими потребностями. Если вы постоянно обнаруживаете, что выполняете одни и те же задачи, такие как копирование данных в Excel, вы можете заставить VBA выполнить эту задачу за вас. Вместо того, чтобы копировать данные с одного рабочего листа вручную и вставлять его в другой, напишите макрос VBA, и вы можете нажать кнопку, чтобы выполнить копирование и мгновенную вставку.

Шаг 1

Шаг 2

Шаг 3

Вырежьте и вставьте следующий код в окно модуля:

Sub Copy_Data () Application.ScreenUpdating = Ложные рабочие листы ("Лист1"). Диапазон ("A1: A10"). Значение = Рабочие листы ("Лист2"). Диапазон ("B1: B10"). Значение Application.ScreenUpdating = True End Sub

Шаг 4

Адаптировать код к вашим конкретным потребностям; как написано, код копирует данные в ячейках с A1 по A10 на листе 1 в ячейки с B1 по B10 на листе 2. Например, если вы хотите скопировать данные из ячеек с C1 по C100 на листе 4 в ячейки с D1 по D100 на листе 5 , вы бы переписали третью строку кода следующим образом:

Шаг 5

Как скопировать программное обеспечение Final Cut Pro с одного компьютера на другой

Final Cut Pro - это программное обеспечение для редактирования видео, которое позволяет пользователям создавать рекламные ролики, фильмы, музыкальные клипы и другие типы видео. Он сочетает в себе аудио и видео и позволяет .

Как перенести данные с одного листа на другой в Excel

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

Как перенести файлы с одного компьютера на другой с помощью кабеля Ethernet

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

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Аннотация

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

Дополнительные сведения

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

Перенос ячейки данных по ячейке
Передача данных в массиве в диапазон ячеек
Передача данных в наборе записей ADO в диапазон ячеек с помощью метода CopyFromRecordset
Создание таблицы QueryTable на Excel, которая содержит результат запроса в источнике данных ODBC или OLEDB
Передача данных в буфер обмена, а затем вклейка содержимого буфера обмена в Excel таблицу

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

Передача данных в текстовый файл с запятой или запятой, который можно Excel позже разбора в ячейки на таблице
Передача данных на таблицу с помощью ADO
Передача данных в Excel с помощью динамических Exchange данных (DDE)

В следующих разделах подробно по каждому из этих решений.

Примечание При использовании Microsoft Office Excel 2007 года можно использовать новый формат файла Excel 2007 (*.xlsx) при сохранения книг. Для этого найдите следующую строку кода в следующих примерах кода:

Замените этот код следующей строкой кода:

Кроме того, база данных Northwind не включена в Office 2007 г. по умолчанию. Однако вы можете скачать базу данных Northwind из Microsoft Office Online.

Использование автоматизации для передачи ячейки данных ячейкой

С помощью автоматизации можно передавать данные на одну ячейку одновременно:

Перенос ячейки данных по ячейкам может быть вполне приемлемым подходом, если объем данных невелик. Вы можете разместить данные в любой точке книги и можете условно отформатировать ячейки во время запуска. Однако этот подход не рекомендуется, если у вас есть большой объем данных для передачи в Excel книгу. Каждый объект Range, приобретаемый во время запуска, приводит к запросу интерфейса, чтобы перенос данных таким образом был медленным. Кроме того, microsoft Windows 95 и Windows 98 имеют ограничение в 64K для запросов интерфейса. Если вы достигнете или превысите это ограничение в 64k для запросов интерфейса, сервер автоматизации (Excel) может перестать отвечать или вы можете получить ошибки, указывающие на низкую память.

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

Дополнительные примеры кода для автоматизации Excel см. в Microsoft Excel Visual Basic.

Автоматизация для передачи массива данных в диапазон на таблице

Массив данных может быть передан сразу нескольким ячейкам:

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

Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, и другой для объекта Range, возвращаемого методом Resize). С другой стороны, для передачи ячейки данных по ячейке потребуются запросы на 300 интерфейсов в объекты Range. По возможности вы можете получать выгоду от передачи данных оптом и уменьшения количества запросов на интерфейс.

Автоматизация для передачи наборов записей ADO в диапазон таблиц

Excel 2000 г. представлен метод CopyFromRecordset, который позволяет переносить набор записей ADO (или DAO) в диапазон на таблице. В следующем коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 г. и передать содержимое таблицы заказов в базе данных образцов Northwind с помощью метода CopyFromRecordset.

Примечание Если вы используете Office 2007 года в базе данных Northwind, необходимо заменить следующую строку кода в примере кода:

Замените эту строку кода следующей строкой кода:

Excel 97 также предоставляет метод CopyFromRecordset, но использовать его можно только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.

Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статьи How to transfer data from an ADO recordset to Excel с помощью автоматизации.

Автоматизация для создания queryTable на таблице

Объект QueryTable представляет таблицу, созданную из данных, возвращаемых из внешнего источника данных. При автоматизации Microsoft Excel можно создать queryTable, просто предоставив строку подключения к OLEDB или источнику данных ODBC вместе с SQL строкой. Excel берет на себя ответственность за создание наборов записей и вставку его в таблицу в заявляемом вами расположении. Использование QueryTables предоставляет ряд преимуществ по сравнению с методом CopyFromRecordset:

Excel обрабатывает создание наборов записей и его размещение в таблицу.
Запрос можно сохранить с помощью QueryTable, чтобы затем обновить его, чтобы получить обновленный набор записей.
При добавлении в таблицу нового queryTable можно указать, что данные, уже существующие в ячейках на этом компьютере, будут перенесены для размещения новых данных (подробнее см. свойство RefreshStyle).

В следующем коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 г., чтобы создать новый queryTable в Excel таблице с использованием данных из базы данных образцов Northwind:

Использование буфера обмена

Буфер Windows также может использоваться в качестве механизма передачи данных на таблицу. Чтобы вклеить данные в несколько ячеек на таблицу, можно скопировать строку, в которой столбцы делимитированы символами вкладок, а строки делимитированы возвращаемой каретой. В следующем коде показано, как Visual Basic использовать объект Clipboard для передачи данных в Excel:

Создание разнонародных текстовых файлов, Excel можно разрезать на строки и столбцы

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

В следующем коде показано, как создать текстовый файл с запятой из наборов записей ADO:

Обратите внимание, что Office версии базы данных Northwind 2007 года необходимо заменить следующую строку кода в примере кода:

Замените эту строку кода следующей строкой кода:

Если в текстовом файле есть расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл запятой. Аналогично, если в вашем файле .TXT расширение, Excel автоматически разборите файл с помощью делимитеров вкладок.

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

Передача данных на таблицу с помощью ADO

С помощью поставщика DB Microsoft Jet OLE можно добавить записи в таблицу в существующей Excel книге. "Таблица" в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заглавные (или имена полей), а все последующие строки содержат записи. Ниже показано, как создать книгу с пустой таблицей MyTable.

Excel 97, Excel 2000 и Excel 2003 г.

Запустите новую книгу в Excel.

Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:

A1: FirstName B1: LastName

Формат ячейки B1 в виде правой выровненной.

В меню Insert выберите Имена, а затем выберите Определение. Введите имя MyTable и нажмите кнопку ОК.

Сохраните новую книгу как C:\Book1.xls и Excel.

Чтобы добавить записи в MyTable с помощью ADO, можно использовать код, аналогичный следующему:

Excel 2007

В Excel 2007 г. запустите новую книгу.

Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:

A1: FirstName B1: LastName

Формат ячейки B1 в виде правой выровненной.

На ленте нажмите вкладку Формулы и нажмите кнопку Определить имя. Введите имя MyTable и нажмите кнопку ОК.

Сохраните новую книгу как C:\Book1.xlsx, а затем Excel.

Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.

При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматированы с правильным выравниванием. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.

Следует отметить, что при добавлении записи в ячейку или ячейки в таблицу она переописывание любых данных, ранее в этих ячейках; Другими словами, строки в таблице не "сдвинуты" при добавлении новых записей. Это следует иметь в виду при разработке макета данных на ваших таблицах.

Метод обновления данных в Excel таблице с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложения в access после установки Office 2003 Пакет обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью Microsoft Knowledge Base 904018. Метод хорошо работает в Visual Basic среде приложений из других Office приложений, таких как Word, Excel и Outlook.

Дополнительные сведения см. в следующей статье:

Дополнительные сведения об использовании ADO для доступа к книге Excel см. в книге How To Query and Update Excel Data Using ADO from ASP.

Использование DDE для передачи данных в Excel

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

Чтобы передать данные в Excel DDE, вы можете использовать метод LinkPoke для отправки данных в определенный диапазон ячейки или метод LinkExecute для отправки команд, которые Excel будут выполняться.

В следующем примере кода показано, как установить беседу DDE с Excel, чтобы можно было втыкать данные в ячейки на таблицу и выполнять команды. С помощью этого примера для успешного запуска беседы по DDE в linkTopic Excel|MyBook.xls книга с именем MyBook.xls должна уже открываться в запущенном экземпляре Excel.

При использовании Excel 2007 можно использовать новый формат .xlsx для сохранения книг. Убедитесь, что имя файла обновляется в следующем примере кода. В этом примере Text1 представляет собой управление текстовым полем на Visual Basic форме:

При использовании LinkPoke с Excel, вы указываете диапазон в строке-столбце (R1C1) для LinkItem. Если вы подтыкаете данные к нескольким ячейкам, можно использовать строку, в которой столбцы делимитированы вкладками, а строки делимитированы возвращаемой каретой.

При использовании LinkExecute для Excel выполнения команды необходимо Excel команду в синтаксисе Excel макроса (XLM). Документация XLM не включается в Excel версии 97 и более поздней версии.
DDE не рекомендуется для общения с Excel. Автоматизация обеспечивает наибольшую гибкость и предоставляет дополнительный доступ к новым функциям, которые Excel предложить.

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