Excel vba перенос строк на другой лист
Добрый вечер.
Есть макрос на перенос строк с одного места на другое. Действие происходит в одном активном листе.
Подскажите пожалуйста, как переделать макрос, чтобы с одного листа перенести на другой лист активной книги по такому же принципу.
[vba]
Sub перенос_строк()
Dim ra As Range: Set ra = Selection
msg1
If ra.Areas.Count <> 2 Then MsgBox msg1, vbCritical, "Ошибка": Exit Sub
ra.Areas(1).Copy
ra.Areas(2).Insert Shift:=xlDown
ra.Areas(1).Delete
End Sub
Добрый вечер.
Есть макрос на перенос строк с одного места на другое. Действие происходит в одном активном листе.
Подскажите пожалуйста, как переделать макрос, чтобы с одного листа перенести на другой лист активной книги по такому же принципу.
[vba]
Sub перенос_строк()
Dim ra As Range: Set ra = Selection
msg1
If ra.Areas.Count <> 2 Then MsgBox msg1, vbCritical, "Ошибка": Exit Sub
ra.Areas(1).Copy
ra.Areas(2).Insert Shift:=xlDown
ra.Areas(1).Delete
End Sub
Sub перенос_строк()
Dim ra As Range: Set ra = Selection
msg1
If ra.Areas.Count <> 2 Then MsgBox msg1, vbCritical, "Ошибка": Exit Sub
ra.Areas(1).Copy
ra.Areas(2).Insert Shift:=xlDown
ra.Areas(1).Delete
End Sub
Sub перенос_строк_на_другой_лист()
Dim rs As Range: Set rs = Application.InputBox("Выделите строки, которые нужно перенести", "перенос_строк_на_другой_лист", , , , , , 8) 'Selection
Dim rd As Range: Set rd = Application.InputBox("Выделите еще однO строку, над которой нужно вставить", "перенос_строк_на_другой_лист", , , , , , 8) 'Selection
rs.Copy
rd.Insert Shift:=xlDown
rs.Delete
End Sub
Sub перенос_строк_на_другой_лист()
Dim rs As Range: Set rs = Application.InputBox("Выделите строки, которые нужно перенести", "перенос_строк_на_другой_лист", , , , , , 8) 'Selection
Dim rd As Range: Set rd = Application.InputBox("Выделите еще однO строку, над которой нужно вставить", "перенос_строк_на_другой_лист", , , , , , 8) 'Selection
rs.Copy
rd.Insert Shift:=xlDown
rs.Delete
End Sub
Sub перенос_строк_на_другой_лист()
Dim rs As Range: Set rs = Application.InputBox("Выделите строки, которые нужно перенести", "перенос_строк_на_другой_лист", , , , , , 8) 'Selection
Dim rd As Range: Set rd = Application.InputBox("Выделите еще однO строку, над которой нужно вставить", "перенос_строк_на_другой_лист", , , , , , 8) 'Selection
rs.Copy
rd.Insert Shift:=xlDown
rs.Delete
End Sub
[/vba](Selection может принадлежать только одному листу, кажется?) Автор - Апострофф
Дата добавления - 17.02.2016 в 07:32
Апострофф, Благодарю за помощь! А нельзя как-нибудь сразу перенести через Cltr? Если убрать Application.InputBox, то начинает ругаться.
Апострофф, Благодарю за помощь! А нельзя как-нибудь сразу перенести через Cltr? Если убрать Application.InputBox, то начинает ругаться. den45444
Здравствуйте, специалисты!
Помогите, пожалуйста, разобраться с макросом в excel. Я не разбираюсь в кодах, но начал заинтересовываться.
Задача:
-Создание архива выполненных задач.
-Перенос строки с первого листа по условию значения "100%" в диапазоне [G:G] на второй лист, затем удаление этой строки с первого листа.
-Уже подсмотрел готовый код на форуме, но он полностью удаляет всё на втором листе при нажатии кнопки макроса. -Нужно, чтобы строки переносились в свободные ячейки, а не замещали имеющиеся.
-То есть при появлении нового значения 100% в ячейке и нажатии кнопки, нужно чтобы это строка вставилась на второй лист в свободную строку, а предыдущие строки на втором листе остались.
Код:
Изменить макрос переноса данных с листа на другой лист
Ребята что в этом коде нужно изменить чтобы он мог все данные в ячейках из Листа1 в пределах 22.
Ускорить действие макроса переноса данных на другой лист
Здравствуйте, имеется макрос для переноса данных на другой лист, но когда данных много (например.
Макрос переноса данных из одного документа в другой
Привет всем и каждому в отдельности) Активно думаю(и работаю) над переносом данных из "Графика".
Перенос строки с первого листа по условию значения "100%" в диапазоне [G:G] на второй лист, затем удаление этой строки с первого листа.
Вопросы:
1. В каком формате те самые значения в столбце G:G. Числовой? Текстовый? Процентный? Общий?
2. Надо ли проверять, есть ли дублирование данной строки на втором листе? И если да, то по каким критериям? Или пофиг - просто копируем, и всё?
3. Могут ли значения в G:G быть больше 100%, и что делать в этом случае? Оставлять и не переносить, или перенос осуществлять во всех случаях, когда >=100% ? Или только в =100%?
4. Есть ли на первом листе строка заголовков. Или проверку на 100% вести с самой первой строки?
5. Что делать, если второй страницы нет?
6. Что делать, если формат ячеек второй страницы не совпадает с форматом ячеек первой? И надо ли их проверять, или пофиг?
Первый лист имеет название "Отдел ПТО"
1. Значения в процентном формате.
2. Проверять не нужно есть ли такая строка на втором листе, ибо такое невозможно в моём случае. Так как эта строка должна удалиться с первого листа после переноса её на второй лист. А одинаковых строк не может быть.
3. Значения не могут быть выше 100% - данные значения это статус выполнения задачи.
4. Да, на первом листе есть строка заголовков. В столбце G этот заголовок "Статус".
5. Второй лист назван "Архив", вряд ли возможна ситуация, когда его нет. В ином случае - создать.
6. Возможно ли как то копировать с первого листа на второй с сохранением формата ячеек первого листа? Желательно, чтобы даты остались датами, проценты процентами.
Вы волшебник! Всё работает как надо, кажется. Как я понял, в этом коде я меняю только "Const HdrRows = 1 'Число строк заголовка - поставьте сюда СВОЁ"? Если одна строка заголовка, соответственно код без изменений. И ".Name = "ИТОГО" 'название 2 страницы" изменяю "итого" на название второго листа? Название первого листа нигде не нужно прописывать?
Добавлено через 25 минут
Возможно ли как-то добавить msgbox после переноса в архив с отображением количества перенесенных строк? Например "В архив перенесено 8 строк".
Добавлено через 4 минуты
Возникла проблема. По началу работает нормально, затем если кликаешь на кнопку при отсутствующих значениях 100% на первом листе, выдаёт ошибку с выделением желтым следующей строки:
"Sheets(1).Rows(aaDel(ii2 - 1)).Delete 'удаляем строки снизу вверх на первой странице" с примечанием "aaDel(ii2 - 1) = Empty".
После этого при добавлении 100% на первом листе и нажатии кнопки, он переносит абсолютно все строки с первого листа на второй.
Добавлено через 6 минут
При нажатии кнопки при отсутствующих значениях "100%" с первого листа, макрос переносит все строки с любыми значениями на второй лист.
Копирует по двойному щелчку строку таблицы одного листа в таблицу на другом листе. Мне нужно похожую, только что бы с первой таблицы копировались строки со 2 по 92 и переносились во вторую таблицу со 2 по 7 строку.
И с первой таблицы копировались с 95 по 220 и переносились во вторую таблицу с 9 по 15 строку.
Еще, но это не обязательно, что бы во второй таблице можно было удалять содержимое строк со 2 по 7 и с 9 по 15 строку двойным щелчком.
Сам переделать не могу, не владею, к сожалению.
Спасибо.
Перенос строк на другой лист по диапазону дат в столбце
Добрый вечер. Помогите,пожалуйста. Есть определенная информация,необходимо по кнопке Отчет по.
Перенос строк на другой лист
Добрый день! Возникла такая ситуация, нужно из одного листа, перенести данные в другой лист, там у.
Перенос всех строк с заданным условием на другой лист
Здравствуйте. Очень нужен макрос, который бы переносил все значения столбцов одной строки по.
Отбор строк в таблице, отвечающих условию и перенос выборки на другой лист
Здравствуйте! Пытаюсь помочь друзьям, совсем не силен в Excel :) Извините если вопрос не в той.
Впихивать не надо. Вторая таблица может очищаться принудительно, а лучше, если при заполнении диапазона строк A2:A7 и A9:A15 следующая строка скопируется снова в A2, A3… или в A9, A10… удалив предыдущее содержание и т. д. Если так сделать сложно, то пусть строки первой таблицы копируются во вторую, начиная с A2 до A15.
Переносит, после полного заполнения каждого целевого диапазона удаляет старое и заполняет по новой.
Форматы копируются, но не удаляются.
На листе "Результат" двойной щелчок обнуляет одну текущую строку.
Переносит, после полного заполнения каждого целевого диапазона удаляет старое и заполняет по новой.
Форматы копируются, но не удаляются.
На листе "Результат" двойной щелчок обнуляет одну текущую строку.
Переносит, после полного заполнения каждого целевого диапазона удаляет старое и заполняет по новой.
Форматы копируются, но не удаляются.
На листе "Результат" двойной щелчок обнуляет одну текущую строку.
Доброго дня! Я к Вам с просьбой о помощи. Суть следующая: есть файл с несколькими листами, первый лист сводный, остальные для заполнения разными менеджерами. В каждом листе ведется учет поступающих документов (одна строка - один документ). Помогите, пожалуйста, написать макрос, чтобы после заполнения новой строки одним из менеджеров он кликал два раза по первой ячейке в данной строке и данные из нее переносились в первую пустую строку первого листа.
Буду Вам очень признателен за помощь.
Добавил новый модуль в книгу, скопировал туда данный код, затем зашел в каждый лист и пробовал два раза кликать по первой ячейке каждой строки, но данные не переносятся. Пробовал добавлять его в каждый лист, тоже не работает. В чем может быть сбой?
Я попробовал использовать код, который указан в начале темы (путем добавления в каждый лист книги). Он работает, но с одной оговоркой: макрос переносит данные из строки по двойному клику в любой ячейке указанной строки, а мне нужно, чтобы переносились данные только при двойном клике по первой ячейке. Это сделано для защиты от случайного переноса данных.
не путайте модуль листа и обычный модуль с модулем книги. Да и сохранить не помешает.
на картинке выделил красным, двойной клик - открывается модуль книги.
не путайте модуль листа и обычный модуль с модулем книги. Да и сохранить не помешает.
на картинке выделил красным, двойной клик - открывается модуль книги.
Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt + Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.
Давайте-ка мы разберёмся в этом вопросе поподробнее.
Удаление переносов строк заменой
Если нам нужно избавиться от переносов, то первое, что обычно приходит в голову - это классическая техника "найти и заменить". Выделяем текст и затем вызываем окно замены сочетанием клавиш Ctrl + H или через Главная - Найти и выделить - Заменить (Home - Find&Select - Replace) . Одна неувязочка - не очень понятно, как ввести в верхнее поле Найти (Find what) наш невидимый символ переноса строки. Alt + Enter тут, к сожалению, уже не работает, скопировать этот символ непосредственно из ячейки и вставить его сюда тоже не получается.
Поможет сочетание Ctrl + J - именно оно является альтернативой Alt + Enter в диалоговых окнах или полях ввода Excel:
Обратите внимание, что после того, как вы поставите мигающий курсор в верхнее поле и нажмёте Ctrl + J - в самом поле ничего не появится. Не пугайтесь - это нормально, символ-то невидимый :)
В нижнее поле Заменить (Replace with) либо ничего не вводим, либо вводим пробел (если хотим не просто удалить переносы, а заменить их на пробел, чтобы строки не склеились в единое целое). Останется нажать на кнопку Заменить всё (Replace All) и наши переносы исчезнут:
Нюанс: после выполнения замены введённый с помощью Ctrl + J невидимый символ остаётся в поле Найти и может помешать в дальнейшем - не забудьте его удалить, установив курсор в это поле и несколько раз (для надёжности) нажав на клавиши Delete и Backspace .
Удаление переносов строк формулой
Если нужно решить задачу именно формулами, то можно использовать встроенную функцию ПЕЧСИМВ (CLEAN) , которая умеет очищать текст от всех непечатаемых символов, включая и наши злополучные переносы строк:
Такой вариант, однако, не всегда бывает удобен, т.к. строки после этой операции могут склеиваться между собой. Чтобы этого не происходило, нужно не просто удалять символ переноса, а заменять его на пробел (см. следующий пункт).
Замена переносов строк формулой
А если хочется не просто удалить, а именно заменить Alt + Enter на, например, пробел, то потребуется уже другая, чуть более сложная конструкция:
Чтобы задать невидимый символ переноса мы используем функцию СИМВОЛ (CHAR) , которая выводит символ по его коду (10). А потом функция ПОДСТАВИТЬ (SUBSTITUTE) ищет в исходных данных наши переносы и заменяет их на любой другой текст, например, на пробел.
Деление на столбцы по переносу строки
Знакомый многим и очень удобный инструмент Текст по столбцам с вкладки Данные (Data - Text to Columns) тоже может замечательно работать с переносами строк и разделить текст из одной ячейки на несколько, разбив его по Alt + Enter . Для этого на втором шаге мастера нужно выбрать вариант пользовательского символа-разделителя Другой (Custom) и использовать уже знакомое нам сочетание клавиш Ctrl + J как альтернативу Alt + Enter :
Если в ваших данных может встречаться несколько переносов строк подряд, то можно их "схлопнуть", включив флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) .
После нажатия на Далее (Next) и прохождения всех трёх шагов мастера мы получим желаемый результат:
Обратите внимание, что до выполнения этой операции необходимо вставить справа от разделяемого столбца достаточное количество пустых колонок, чтобы образовавшийся текст не затёр те значения (цены), которые были справа.
Деление на строки по Alt+Enter через Power Query
Ещё одной любопытной задачей является деление многострочного текста из каждой ячейки не на столбцы, а на строки:
Вручную такое делать долго, формулами - сложно, макросом - не каждый напишет. А на практике подобная задача встречается чаще, чем хотелось бы. Самым простым и лёгким решением будет использовать для этой задачи возможности надстройки Power Query, которая встроена в Excel начиная с 2016 года, а для более ранних версий 2010-2013 её можно совершенно бесплатно скачать с сайта Microsoft.
Чтобы загрузить исходные данные в Power Query их нужно сначала преобразовать в "умную таблицу" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) . Если по каким-то причинам вы не хотите или не можете использовать "умные таблицы", то можно работать и с "глупыми". В этом случае просто выделите исходный диапазон и дайте ему имя на вкладке Формулы - Диспетчер имен - Создать (Formulas - Name Manager - New) .
После этого на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) можно жать на кнопку Из таблицы / диапазона (From Table/Range) , чтобы загрузить нашу таблицу в редактор Power Query:
После загрузки выделим столбец с многострочным текстом в ячейках и выберем на Главной вкладке команду Разделить столбец - По разделителю (Home - Split Column - By delimiter) :
Чтобы всё разделилось на строки, а не не столбцы - не забудьте переключить селектор Строки (By rows) в группе расширенных параметров.
Останется только нажать на ОК и получить желаемое:
Готовую таблицу можно выгрузить обратно на лист с помощью команды Закрыть и загрузить - Закрыть и загрузить в. на вкладке Главная (Home - Close&Load - Close&Load to. ) .
Важно отметить, что при использовании Power Query необходимо помнить о том, что при изменении исходных данных результаты автоматически не обновляются, т.к. это не формулы. Для обновления нужно обязательно щёлкнуть правой кнопкой мыши по итоговой таблице на листе и выбрать команду Обновить (Refresh) или нажать кнопку Обновить всё на вкладке Данные (Data - Refresh All) .
Макрос для деления на строки по Alt+Enter
Для полноты картины давайте упомянем решение предыдущей задачи ещё и с помощью макроса. Откройте редактор Visual Basic с помощью одноимённой кнопки на вкладке Разрабочик (Developer) или сочетания клавиш Alt + F11 . В появившемся окне вставьте новый модуль через меню Insert - Module и скопируйте туда нижеприведённый код:
Вернитесь в Excel и выделите ячейки с многострочным текстом, который надо разделить. Затем воспользуйтесь кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 , чтобы запустить созданный макрос, который и проделает за вас всю работу:
Вуаля! Программисты - это, на самом деле, просто очень ленивые люди, которые лучше один раз как следует напрягутся, чтобы потом ничего не делать :)
На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:
Исходная таблица задания №1
Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):
Часть результирующего списка задания №1
Решение копированием с листа на лист
Это решение значительно проще, чем с использованием массивов, но более медленное. При больших объемах информации обработка может длиться достаточно долго. Решение достигается путем присваивания значений ячеек из таблицы первого листа ячейкам второго листа.
Переменные:
- 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 программного модуля.
Читайте также: