Копирование заданного диапазона из нескольких листов в новый лист

У Ом есть сотни листов с разными именами в одной книге. Структура каждого рабочего листа по сути одинакова. Он хочет скопировать фиксированный диапазон (A146: O146) из каждого листа в новый лист, одну строку за другой.

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

  1. Отобразите вкладку «Формулы» на ленте.
  2. В группе «Определенные имена» щелкните инструмент «Диспетчер имен». Excel отображает диалоговое окно «Диспетчер имен».
  3. Нажмите кнопку «Создать». Excel отображает диалоговое окно «Новое имя». (См. Рис. 1.)
  4. Рис. 1. Диалоговое окно «Новое имя».

  5. В поле «Имя» введите имя ListSheets (обратите внимание, что это одно слово с без пробелов).
  6. В поле “Ссылается на” введите следующую формулу:
  7.  = REPLACE (GET.WORKBOOK (1), 1, FIND ("]"  , GET.WORKBOOK (1), 1), "") 
  8. Нажмите кнопку ОК, чтобы завершить создание именованного диапазона. Новый диапазон должен появиться в диалоговом окне «Диспетчер имен».
  9. Нажмите кнопку «Закрыть», чтобы закрыть диалоговое окно «Диспетчер имен».

С определенной формулой таким образом вы можете затем перейти к рабочему листу, на котором вы хотите объединить все эти диапазоны. (Допустим, этот рабочий лист называется «Сводка».) Я настоятельно рекомендую убедиться, что этот рабочий лист является самым последним в вашей книге. На листе сводки поместите следующую формулу в столбец A любой строки:

 = INDIRECT (INDEX (ListSheets, ROWS ($ A $ 1: $ A1)) & "!" & CELL ("адрес  ", A $ 146)) 

Скопируйте формулу вниз, сколько бы строк ни потребовалось для представления всех листов. Другими словами, если у вас есть 25 листов (не считая итогового листа), скопируйте формулу на 24 строки вниз. Подсчитав оригинал, теперь у вас должна появиться формула в общей сложности в 25 строках.

Примечание: формула ListSheets – та, которую вы определили в диспетчере имен – возвращает массив рабочего листа имена. Функция ROWS используется для определения того, какой элемент этого массива возвращается через функцию INDEX. Если ваш итоговый рабочий лист не последний в вашей книге, он может быть легко возвращен ListSheets, и вы в конечном итоге получите из него значения. Вы, несомненно, не хотите этого делать, поэтому я предложил убедиться, что Summary был последним листом в книге.

Теперь просто скопируйте формулы из столбца A вправо, чтобы у вас есть они на всем протяжении столбца O. В результате вы будете иметь значения из A146: O146 в ячейках, содержащих формулы.

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

 Sub CopyRange () Dim w As Worksheet Dim sRange As String Dim lRow As Long sNewName = "Summary" 'Имя сводного рабочего листа sRange =  "A146: O146" 'Диапазон для копирования из каждого рабочего листа Рабочие листы (1). Выберите рабочие листы. Добавить ActiveSheet.Name = sNewName lRow = 2 For Each w In Worksheets If w.Name  sNewName Then' Закомментируйте следующую строку, если вы  не хотите 'включать имена рабочих листов в сводный лист Cells (lRow, 1) = w.Name' Если вы закомментировали предыдущую строку, внесите изменения 'в следующую строку: change (lRow, 2) to (lRow  , 1) w.Range (sRange) .Copy Cells (lRow, 2) lRow = lRow + 1 End If Next wEnd Sub 

Обратите внимание, что установлены две переменные (sNewName и sRange) в начале макроса. Они представляют имя, которое вы хотите использовать для нового итогового рабочего листа, созданного макросом, и диапазон ячеек, который вы хотите скопировать с каждого рабочего листа.

Затем макрос делает первый рабочий лист в книге активным и добавляет новый рабочий лист, который будет использоваться для обобщения. Этому листу назначается имя, указанное вами в переменной sNewName. Затем макрос проходит цикл, проверяя каждый из других листов. Пока рабочий лист не является сводным, имя рабочего листа помещается в столбец A сводного листа, а диапазон, указанный в переменной sRange (A146: O146), копируется в сводный рабочий лист, начиная со столбца B. .

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

Оцените статью
Frestage.ru
Добавить комментарий