Excel копирование отобранных данных допускается только на тот же лист
Как сохранить отфильтрованные данные на новый лист / книгу в Excel?
При копировании отфильтрованного диапазона в Excel по умолчанию копируются только видимые ячейки, а затем мы можем вручную вставить его в новый рабочий лист. Однако высота строки и ширина столбца не зарезервированы. Следовательно, как сохранить отфильтрованные данные на новый лист или книгу и одновременно сохранить высоту строки и ширину столбца в Excel? Здесь мы познакомим вас с парой методов.
Сохраняйте отфильтрованные данные на новый лист с функциями копирования и вставки
Обычно мы можем применить Копировать функции и макаронные изделия функция для сохранения отфильтрованных данных напрямую в новый рабочий лист или книгу. Вы можете сделать следующее:
1. Создайте новый рабочий лист, щелкнув значок Добавить кнопка or на панели вкладок листа.
Ноты:
(1) Нажатие Shift + F11 одновременно с клавишами можно также создать новый рабочий лист.
(2) Если вы собираетесь сохранить отфильтрованные данные в новую книгу, просто нажмите Ctrl + N клавиши одновременно, чтобы создать новую книгу.
2. Выберите отфильтрованные данные и скопируйте их, нажав Ctrl + C одновременно.
3. Перейдите к новому листу или книге, выберите пустую ячейку и нажмите Ctrl + V одновременно клавиши, чтобы вставить отфильтрованные данные.
Затем вы увидите, что только отфильтрованные данные копируются и вставляются в новый рабочий лист / книгу. Однако высота строки и ширина столбца теряются. См. Снимки экрана ниже:
Простое разделение диапазона на основе критериев в столбце и сохранение разделенных диапазонов на новых новых листах
Kutools для Excel Разделить данные Утилита может помочь пользователям Excel легко разделить диапазон на несколько листов на основе критериев в одном столбце исходного диапазона. Полнофункциональная бесплатная 30-дневная пробная версия!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Сохраняйте отфильтрованные данные на новый лист с помощью функции копирования и перемещения
Если отфильтрованные данные слишком велики, чтобы их можно было легко выбрать и скопировать, и вы настроили ширину столбцов и высоту строк для многих столбцов и строк, копирование и вставка могут быть не лучшим решением. В этих условиях вы должны попробовать Kutools for Excel's Удалить скрытые (видимые) строки и столбцы утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Щелкните правой кнопкой мыши имя листа, который содержит отфильтрованные данные, на панели вкладок листа и выберите Переместить или скопировать из контекстного меню.
Шаг 2. Во всплывающем диалоговом окне «Перемещение или копирование» укажите книгу в Переместить выбранные листы в книгу раскрывающийся список, проверьте Создать копию и нажмите OK кнопку.
Внимание: Чтобы сохранить отфильтрованные данные на новом листе, выберите имя текущей книги в Переместить выбранные листы в книгу раскрывающийся список; чтобы сохранить отфильтрованные данные в новую книгу, выберите (новая книга).
3. Теперь вы попадете в новый рабочий лист / книгу, нажмите Кутулс > Удалить > Удалить скрытые (видимые) строки и столбцы, а затем в открывшемся диалоговом окне
(1) Выберите В активном листе в Заглянуть раскрывающийся список;
(2) Проверить Ряды вариант в Удалить тип раздел;
(3) Проверить Скрытые строки вариант в Подробный тип раздел;
(4) Щелкните значок Ok кнопку.
Теперь вы увидите, что все отфильтрованные данные зарезервированы на новом листе, а все форматирование ячеек, включая высоту строк и ширину столбцов, сохраняется.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: сохранение отфильтрованных данных на новый лист в Excel
Как скопировать данные на другой лист с помощью расширенного фильтра в Excel?
Скопируйте данные на другой лист с помощью расширенного фильтра с помощью функции расширенного фильтра
Вот простой способ решения этой задачи: сделайте следующее:
1. Активируйте рабочий лист, на который вы хотите скопировать отфильтрованный результат, с помощью функции расширенного фильтра.
2. Затем нажмите Данные > Дополнительно, см. снимок экрана:
3. В Расширенный фильтр диалоговое окно, выберите Скопировать в другое место из Действие раздел, а затем щелкните кнопку, чтобы выбрать диапазон данных, который вы хотите отфильтровать, на другом листе рядом с Диапазон списка, продолжайте нажимать кнопка рядом с Диапазон критериев чтобы выбрать критерии из другого листа, см. снимок экрана:
4. Затем нажмите кнопка рядом с Скопировать в поле, чтобы выбрать ячейку, в которую вы хотите вывести отфильтрованный результат из активного листа, а затем щелкните OK Кнопка, отфильтрованный результат был извлечен на другой лист на основе вашей исходной таблицы данных.
Скопируйте данные на другой лист с помощью расширенного фильтра с помощью кода VBA
Если у вас есть опыт работы с кодом VBA, вы можете завершить эту работу с помощью следующего кода VBA.
1. Активируйте рабочий лист, который вы хотите отфильтровать, и скопируйте результат на другой лист.
2. Удерживайте ALT + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.
3. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: скопируйте данные на другой лист с помощью расширенного фильтра:
4. And then press F5 key to run this code, and a prompt box is popped out to let you select the data range that you want to filter, see screenshot:
5. Then click OK, and another dialog box is popped out to remind you to select the criteria range you want to filter based on, see screenshot:
6. Go on clicking OK, in the popped out dialog box, please select a cell from another new worksheet where you want to output the filter result, see screenshot:
7. At last, click OK button, the filtered data has been outputted into another specified sheet as you need.
Как скопировать данные из / на несколько листов в Excel?
Обычно копировать и вставлять данные в Excel может быть несложно. Но знаете ли вы, как быстро скопировать ряд данных и вставить их на несколько листов? А что если пакетное копирование данных с нескольких листов? Следующие методы помогут вам легко их решить.
- Скопируйте и вставьте данные в один и тот же диапазон нескольких листов
- Скопируйте одну и ту же ячейку с нескольких листов
- Скопируйте все данные с нескольких листов на один лист
Скопируйте и вставьте данные в один и тот же диапазон нескольких листов
Мы можем скопировать ряд данных и вставить их на несколько листов с Заполнять >На рабочих листах функция в Excel. Пожалуйста, сделайте следующее:
1. Выберите диапазон на текущем листе, который вы скопируете и вставите на несколько листов.
2. Перейдите на панель вкладок листа и выберите несколько листов (включая текущий лист), в которые вы будете вставлять данные. Смотрите скриншот:
Внимание: Держа Ctrl key, вы можете выбрать несколько несмежных листов, щелкая каждый лист один за другим; проведение Shift key, вы можете выбрать несколько соседних листов, щелкнув первый и последний лист.
3. Нажмите Главная > Заполнять > На рабочих листах. Смотрите скриншот:
4. В открывшемся диалоговом окне «Заливка на всех листах» выберите один вариант, соответствующий вашим потребностям, и нажмите кнопку OK кнопку.
В нашем случае мы проверяем Все и нажмите OK кнопка. Смотрите скриншот:
Затем выбранный диапазон данных копируется из текущих листов и вставляется в другие выбранные листы.
Скопируйте одну и ту же ячейку / диапазон из нескольких листов
Этот метод представит Kutools for Excel's Динамически обращаться к рабочим листам утилита для копирования одной и той же ячейки с нескольких листов в Excel.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Создайте новый рабочий лист, щелкнув значок Новое кнопка or на панели вкладок листа.
2. Выберите ячейку (говорит ячейка D7) в новом созданном листе, содержимое ячейки которой вы скопируете с другого листа, и нажмите Кутулс > Еще (в Формула группа)> Динамически ссылаться на рабочий лист. Смотрите скриншот:
3. В открывшемся диалоговом окне «Заполнить рабочие листы», пожалуйста, (1) выберите вариант из Заполнить заказ раскрывающийся список; (2) проверьте рабочие листы, из которых вы будете копировать содержимое ячеек в Список рабочих листов раздел; а также (3) нажмите Диапазон заполнения и Закрыть кнопку последовательно. Смотрите скриншот:
Затем содержимое той же ячейки копируется из указанных листов в текущий лист. Смотрите скриншот:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Скопируйте все данные с нескольких листов на один лист
Этот метод говорит об обходном пути для копирования всех данных с нескольких листов, а затем их вставки в новый рабочий лист с помощью Kutools for Excel's Объединить (рабочие листы) утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Нажмите Кутулс Плюс > Сочетать , чтобы открыть диалоговое окно «Объединить листы».
2. В открывшемся мастере объединения рабочих листов - шаг 1 из 3 проверьте Объедините несколько листов из книги в один лист и нажмите Далее кнопка. Смотрите скриншот:
3. В мастере объединения рабочих листов - шаг 2 из 3 проверяйте только текущую книгу в Список рабочих книг раздел; проверьте рабочие листы, из которых вы будете копировать данные в Список рабочих листов и нажмите Далее кнопку.
Внимание: тот же диапазон Кнопка может помочь вам быстро указать на каждом листе тот же диапазон, что и диапазон листа, который вы выбрали в Список рабочих листов .
4. В мастере объединения листов - шаг 3 из 3 настройте нужные параметры и нажмите кнопку Завершить кнопку.
5. Затем появляется диалоговое окно «Объединить рабочие листы» с просьбой сохранить сценарии объединения. Пожалуйста, нажмите на Да или Нет кнопку как вам нужно. В нашем случае мы нажимаем кнопку Нет кнопку.
Пока мы скопировали данные со всех выбранных листов и вставили в новую книгу.
Демонстрация: копирование данных из / на несколько листов в Excel
Этот способ хорош тем, что в данном случае мы не подключаем дополнительных библиотек, а пользуемся только предустановленным функционалом. Способ подойдет, если вам необходимо просто получить список файлов в папке и не нужно выводить их размер, определять тип файла, дату его создания/изменения и т.п. Тут мы получаем только наименования файлов и больше ничего.
Вот там код данной функции, который выводит на лист 1 перечень файлов.
Тут мы проверяем существование папки с помощью дополнительной функции. Опять таки не используем сторонних библиотек, а используем туже функцию Dir.
Вот так выглядит результат работы функции. Программа записала список файлов на текущий лист Excel.
Здесь мы не делали проверку на тип файла и вывели все файлы которые у нас были.
Если же необходимо отобрать только определенные типы файлов, например Excel файлы, то в нашем коде необходимо сделать дополнительную проверку:
Знак “*” означает любой набор символов. Таким образом, мы учли различные версии файлов Excel (xls, xlsx, xlsm).
Условие задачи по копированию данных
На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:
Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):
Создание новых листов
Создание новых рабочих листов осуществляется с помощью метода Sheets.Add.
Синтаксис метода Sheets.Add
expression.Add [Before, After, Count, Type]
где expression – переменная, представляющая собой объект Sheet.
Компоненты метода Sheets.Add
- Before* – необязательный параметр типа данных Variant, указывающий на лист, перед которым будет добавлен новый.
- After* – необязательный параметр типа данных Variant, указывающий на лист, после которого будет добавлен новый.
- Count – необязательный параметр типа данных Variant, указывающий, сколько листов будет добавлено (по умолчанию – 1).
- Type – необязательный параметр типа данных Variant, указывающий тип листа: xlWorksheet** (рабочий лист) или xlChart (диаграмма), по умолчанию – xlWorksheet.
*Если Before и After не указаны, новый лист, по умолчанию, будет добавлен перед активным листом.
**Для создания рабочего листа (xlWorksheet) можно использовать метод Worksheets.Add, который для создания диаграмм уже не подойдет.
Примеры создания листов
- Лист1 в After:=Лист1 – это уникальное имя листа, указанное в проводнике редактора VBA без скобок.
- Лист1 в After:=Worksheets(«Лист1») – это имя на ярлыке листа, указанное в проводнике редактора VBA в скобках.
Создаваемый лист можно присвоить объектной переменной:
Если создаваемый лист присваивается объектной переменной, он будет помещен перед активным листом. Указать дополнительные параметры невозможно.
Как макросом скопировать листы в Excel
Допустим необходимо приготовить планы работ для сотрудников вашего отдела. Иметься шаблон таблицы для заполнения документа плана в виде одного рабочего листа Excel:
Но вам необходимо создать 12 планов и соответственно 12 листов. В программе Excel нет встроенного инструмента для многократного создания копий рабочих листов за одну операцию. А копировать и вставлять 12 (а в практике встречаются случаи что и 120) листов вручную, да еще их все нужно переименовать – это потребует много рабочего времени и пользовательских сил. Определенно лучше в таком случае воспользоваться собственным макросом. А чтобы его написать воспользуйтесь VBA-кодом, который будет представлен ниже в данной статье.
Сначала откройте редактор макросов Visual Basic:
Создайте в нем стандартный модуль с помощью опций меню: «Insert»-«Module» и введите в него этот код, который ниже представленный на листинге:
Sub CopyList()
Dim kolvo As Variant
Dim i As Long
Dim list As Worksheet
kolvo = InputBox( "Укажите необходимое количество копий для данного листа" )
If kolvo = "" Then Exit Sub
If IsNumeric(kolvo) Then
kolvo = Fix(kolvo)
Set list = ActiveSheet
For i = 1 To kolvo
list.Copy after:=ActiveSheet
ActiveSheet.Name = list.Name & i
Next
Else
MsgBox "Неправильно указано количество"
End If
End Sub
Теперь если нам нужно скопировать 12 (или любое другое количество) раз листов содержащие шаблон для заполнения плана работы сотрудника, кликните по исходному листу, чтобы сделать его активным и выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«CopyList»-«Выполнить». Сразу после запуска макроса появиться диалоговое окно, в котором следует указать количество копий листа:
Введите, например, число 12 и нажмите ОК:
Лист с шаблоном плана скопируется 12 раз, а все названия листов будут иметь такое же как название исходного листа только со своим порядковым номером от 1 до12.
Внимание! Если название исходного листа слишком длинное, тогда может возникнуть ошибка в процессе выполнения макроса. Ведь в Excel название листа не может содержать более чем 31 символ. То есть ориентируйтесь так чтобы название исходного листа было меньше чем 27 символов. Так же ошибка может возникнуть если текущая рабочая книга Excel уже содержит листы с таким названием как у копий. Ведь в Excel все листы должны иметь уникальные названия.
Примечание. В новых версиях Excel (начиная от 2010 версии) одна рабочая книга может содержать максимальное количество листов, которое ограничивается лишь размером свободной оперативной памяти системы.
Что нельзя сделать с помощью макрорекодера?
Макро-рекордер отлично подходит для вас в Excel и записывает ваши точные шаги, но может вам не подойти, когда вам нужно сделать что-то большее.
- Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
- Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
- Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
- Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).
Что такое макрос?
Для начала немного о терминологии.
Макрос – это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.
Макрорекодер – это инструмент в Excel, который пошагово записывает все что вы выполняете в Excel и преобразует это в код на языке VBA. Макрорекодер создает очень подробный код (как мы увидим позже), который вы сможете при необходимости отредактировать в дальнейшем.
Записанный макрос можно будет запускать неограниченное количество раз и Excel повторит все записанные шаги. Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто записав свои шаги и затем повторно использовать их позже.
Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.
Решение с использованием массивов
Циклы в массивах работают очень быстро, но решение с ними может быть сложнее, чем при простом присваивании значений одних ячеек другим. В этом примере для упрощения кода две процедуры записаны отдельно и в нужные моменты вызываются для исполнения.
,
нужные адреса прописываются, но вместо закрытых выдает пустые строки, от которых я и пытаюсь избавиться.
Я понимаю, что все это возможно делать при помощи обычного фильтра, но это не устраивает.
Также не хотелось бы иметь дополнительных листов, т.к. количество листов в документе и так зашкаливает, я их отсюда убрал, чтобы не загромождать.
Прошу помочь решить этот вопрос формулой, если это возможно.
Заранее спасибо.
Вложение 616372
Копирование содержимого ячейки на другой лист по условию
Привет великим гуру владеющим в совершенстве Excel. В программе не силен, поэтому вопрос глупый.
Копирование строк из таблицы на другой лист по условию
Доброй ночи! Помогите. Нужно чтобы автоматом происходило копирование строк из одного листа в.
Решение
Залил еще раз файл, сейчас проверю формулу.
Попробовал формулу, заменил в соответствующих столбцах буквы, все заработало, спасибо огромное.
Добавлено через 3 минуты
Вы наверное удалили со сдвигом?! Если да то смотрите на диапазон используемый в формуле. Сделайте чтобы был везде один и тот же диапазон.
Ну да, просто очистка мне не нужна.
А насчет диапазона вы правы, не углядел, почему то из 3 диапазонов, 2 автоматом исправились, а 1 остался без изменения, вот и получилось так.
Добавлено через 8 минут
Все поправил, все работает, огромное спасибо за помощь, всего вам наилучшего.
"Есть файл (во вложении), в котором существует огромный список адресов (каждая строка это отдельный адрес с кучей нужной информации по нему).
Каждая строка (т.е. каждый адрес) имеет 2 состояния (столбец "H") "Закрыт" и "В работе".
Задача у меня такая: скопировать на другой лист, который "ОТК", все строки, имеющие статус "В работе"."
Теперь же появилось третье состояние этого столбца (в примере P) - "На выплату" (дополнительно меня нагрузили теперь бухгалтерскими вопросами) и данная формула не помогает:
, т.к. в листе ОТК теперь выводятся и объекты в работе и на выплату.
Есть 2 вопрос, уже бухгалтерский: в том же примере есть окно "Бухгалтерия", в котором я пытался автоматизировать подсчет зарплаты. Т.е. мне надо, чтобы все объекты со статусом "На выплату" автоматически забивались в ведомость, соответственно если одна фамилия встречается более 1 раза, зарплата суммировалась.
Буду благодарен, если кто поможет решить задачу или подскажет, как её победить.
Заранее спасибо.
Читайте также: