Excel вставить диапазон на другой лист
да-да! я уже исправил! однако возникла новая трабла!
вот код:
Private Sub CommandButton1_Click()
Dim nomer As Long
nomer = Worksheets("данные").Range("p1") 'переменная получает количество строк для обработки
For y = 2 To nomer
If Worksheets("данные").Cells(10, y) = "" Then
Worksheets("данные").Range("a" & y & ":n" & y).Copy Worksheets("формы").Cells(Rows.Coun t, 1).End(xlUp).Offset(1)
по идее если в столбце "p" на листе "данные" нет значений, то выводиться на лист "формы" должны все заполненные строки, так нет же! воводятся не все и не понятно по каким признакам.
Всем привет! Помогите, пожалуйста, правильно написать код.
Дано: два листа одной книги, на 1 листе имеются две таблицы с пустыми строками между ними, на 2 листе данные. Диапазоны на обоих листах динамичны по количеству строк. Копируемый диапазон занимает столбцы A:AC.
Нужно скопировать данные со 2го листа в первую пустую ячейку после первой таблицы на первом листе.
По моему коду он копирует, вот только в самый низ 2 таблицы (что логично по коду). Как сделать, чтобы он вставлял данные в ячейку после 1 таблицы? В начале кода я Ее уже нашла, но хоть убей не могу понять как правильно указать на неё ссылку в последней строке кода.
Dim wr, wm as Worksheet
Dim rng1, rng2 as range
Dim lastrow1, lastrow2, lrow1 as Long
Set wr = ThisWorkbook.Worksheets ("R")
wr.Activate
lastrow1 = Columns (1).Find(Empty, Cells(Rows.Count, 1), xlValues, xlWhole)
Set wm = ThisWorkbook.Worksheets ("M")
wm.activate
lastrow2 = wm.cells(Rows.Count, 1).End(xlUp).row
lrow1 = lastrow1 + lastrow2
Set rng2 = wm.range(Cells(1,1), Cells(lastrow2, 29))
wr.Activate
rng2.Copy wr.Cells(Rows.Count, 1).End(xlUp).Rows
В конце я пробовала и Cells(lastrow1, 1) и range ("a" & lastrow1 & "ac" & lrow1), все мимо(
Очень надеюсь на вашу помощь.)
Возможность использования значений с других листов Excel позволяет получать информацию в упрощенном виде. Другими словами, Вы можете на одном листе написать много значений, а вычисления по данным значениям проводить на другом листе.
Как лично я использую данную опцию в работе? Считаю свою зарплату :) В конце каждого месяца приходит расчетный лист, я делаю ссылку на него и вывожу все необходимые данные на один общий лист с подсчетами.
Так что пользуйтесь на здоровье в любых целях! Главное, чтобы Вам это пригодилось! А лист с зарплатой показывать не буду :) Но поверьте он есть!
Разберем простой пример.
В прошлой статье по графикам в Excel я показал таблицу. Чтобы не придумывать новые значения, просто воспользуемся ей. Она расположена на листе 4 моего документа.
1. Создадим новый лист (Лист5 в моем случае). Кто не знает как это делать - нажать на плюс возле последнего листа в вашем документе. Для удобства напишем какие данные будем выводить:
2. Чтобы получить значение заданной ячейки из листа 4, нужно выбрать любую ячейку, поставить знак " = ", внизу нажать на лист4 и выбрать нужную ячейку.
Я решил немного усложнить задачу и не просто выбрать одну ячейку, а найти сумму значений нескольких ячеек. Для этого необходимо в пустой ячейке начать писать " =СУММ( " и уже после этого перейти на лист4 :
3. Попадаем на лист 4, находим нужный массив чисел и выделяем его. Вверху автоматически формируется формула:
Разберем формулу для тех кто не понял:
- Знак равно означает начало функции. Так программа понимает, что необходимо будет выполнить какое-то действие.
- Слово " СУММ " означает, что необходимо будет выполнить сложение для выражения, указанного в круглых скобках.
- Слово " Лист4! " означает, что данные будут браться именно с Листа 4, ведь на других листах есть ячейки с такими же названиями.
- Слова " C21:I21 " - это диапазон значений, то есть программа начнет с первой указанной ячейки и будет повторять действие до последней указанной ячейки.
Нажимаем " Enter " и программа переносит нас к результату:
Теперь мы знаем сумму данных за указанный период (1941г). Аналогичным образом я сделал для 1944 года:
Используйте команды Вырезать, Копировать и Вставить, чтобы перемещать или копировать содержимое ячеек. Так можно копировать определенную часть содержимого или свойств ячеек. Например, скопировать вычисленное по формуле значение, не копируя саму формулу, или скопировать только формулу.
Ячейки в Excel перемещаются и копируются вместе с формулами, итоговыми значениями, форматом и примечаниями.
В Excel ячейки можно перемещать путем перетаскивания или с помощью команд Вырезать и Вставить.
Перемещение ячеек путем перетаскивания
Выделите ячейки или диапазон ячеек, которые требуется переместить или скопировать.
Наведите указатель мыши на границу выделенной области.
Когда указатель примет вид указателя перемещения , перетащите ячейку или диапазон ячеек в другое место.
Перемещение ячеек с помощью команд "Вырезать" и "Вставить"
Выделите ячейку или диапазон ячеек.
Выделите ячейку, в которую вы хотите переместить данные.
На вкладке Главная нажмите кнопку Вставить или нажмите клавиши CTRL+V.
Копируйте ячейки на листе с помощью команд Копировать и Вставить.
Выделите ячейку или диапазон ячеек.
Выделите ячейки, которые нужно переместить или скопировать.
На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.
Чтобы переместить ячейки, нажмите кнопку Вырезать .
Сочетание клавиш Можно также нажать клавиши CTRL+X.
Чтобы скопировать ячейки, нажмите кнопку Копировать .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.
Выделите левую верхнюю ячейку области вставки.
Совет: Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.
На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+V.
В Excel вырезанные или скопированные ячейки выделяются анимированной пунктирной рамкой. Чтобы снять это выделение, нажмите клавишу ESC.
При перемещении ячеек путем вырезания и вставки в приложении Excel замещаются существующие данные в области вставки.
Чтобы воспользоваться специальными вариантами вставки ячеек, можно щелкнуть стрелку под кнопкой Вставить и выбрать нужный вариант. Например, вы можете нажать Специальная вставка или Рисунок.
По умолчанию в приложении Excel на листе отображается кнопка Параметры вставки, которая позволяет получить доступ к специальным вариантам вставки ячеек, например Сохранить исходное форматирование. Если не нужно отображать эту кнопку при каждой вставке ячеек, ее можно отключить. Откройте вкладку Файл и выберите команду Параметры. В категории Дополнительно в разделе Вырезание, копирование и вставка снимите флажок Отображать кнопку параметров вставки при вставке содержимого.
По умолчанию режим перетаскивания включен, поэтому можно перемещать и копировать ячейки с помощью мыши.
Выделите ячейки или диапазон ячеек, которые требуется переместить или скопировать.
Выполните одно из указанных ниже действий.
Для перемещения ячейки или диапазона ячеек поместите указатель на границу выделенного фрагмента. Когда указатель примет вид указателя перемещения , перетащите ячейку или диапазон ячеек в другое место.
Чтобы скопировать ячейки или диапазон ячеек, поместите указатель на границу выделенного фрагмента, удерживая нажатой клавишу CTRL. Когда указатель примет форму указателя копирования , перетащите ячейку или диапазон ячеек в новое расположение.
Примечание: При перемещении ячеек в приложении Excel замещаются существующие данные в области вставки.
При копировании ячеек ссылки на ячейки изменяются автоматически. Но при перемещении ячеек ссылки не изменяются, и вместо содержимого этих ячеек и всех ячеек, которые на них ссылаются, может отображаться ошибка адресации. В этом случае потребуется изменить ссылки вручную.
Если выделенная область копирования содержит скрытые ячейки, строки или столбцы, приложение Excel копирует их. Может понадобиться временно отобразить данные, которые не нужно копировать.
Вставка скопированных и перемещенных ячеек между существующими ячейкамиВыделите ячейку или диапазон ячеек с данными, которые нужно переместить или скопировать.
На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.
Чтобы переместить выделенный фрагмент, нажмите кнопку Вырезать .
Сочетание клавиш Можно также нажать клавиши CTRL+X.
Чтобы скопировать выделенный фрагмент, нажмите кнопку Копировать .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.
Щелкните правой кнопкой мыши левую верхнюю ячейку области вставки, а затем выберите пункт Вставить вырезанные ячейки или Вставить скопированные ячейки.
Совет Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.
В диалоговом окне Вставка скопированных ячеек выберите направление, в котором нужно сдвинуть окружающие ячейки.
Заметка При вставке целых строк или столбцов окружающие строки и столбцы сдвигаются соответственно вниз и влево.
Если часть ячеек, строк или столбцов на листе не отображается, можно скопировать все ячейки или только видимые. Например, можно скопировать только отображаемые итоговые данные на листе со структурой.
Выделите ячейки, которые вы хотите скопировать.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить и выберите команду Выделить группу ячеек.
В группе Выбор щелкните элемент Только видимые ячейки и нажмите кнопку ОК.
На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.
Выделите левую верхнюю ячейку области вставки.
Совет: Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.
На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+V.
Если нажать стрелку под командой Вставить , можно выбрать параметры вставки, которые следует применить к выделенному диапазону.
При копировании значения последовательно вставляются в строки и столбцы. Если область вставки содержит скрытые строки или столбцы, возможно, потребуется отобразить их, чтобы увидеть все скопированные данные.
При копировании и вставке скрытых или отфильтрованных данных в другое приложение или в другой экземпляр приложения Excel будут скопированы только видимые ячейки.
Предотвращение замещения данных скопированными пустыми ячейкамиВыделите диапазон ячеек, содержащий пустые ячейки.
На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.
Выделите левую верхнюю ячейку области вставки.
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выберите вариант Специальная вставка.
Установите флажок пропускать пустые ячейки.
Перемещение и копирование только содержимого ячейкиДважды щелкните ячейку с данными, которые нужно переместить или скопировать.
Заметка По умолчанию можно редактировать и выделять данные непосредственно внутри ячейки, дважды щелкнув ее, но можно редактировать и выделять данные и в строка формул.
В ячейке выделите символы, которые нужно скопировать или переместить.
На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.
Чтобы переместить выделенный фрагмент, нажмите кнопку Вырезать .
Сочетание клавиш Можно также нажать клавиши CTRL+X.
Чтобы скопировать выделенный фрагмент, нажмите кнопку Копировать .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.
Щелкните в том месте ячейки, куда нужно вставить символы, или дважды щелкните другую ячейку для перемещения или копирования в нее данных.
На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+V.
Нажмите клавишу ВВОД.
Примечание: При двойном щелчке ячейки или нажатии клавиши F2 для редактирования активной ячейки клавиши со стрелками будут работать только внутри данной ячейки. Чтобы с помощью клавиш со стрелками переместиться в другую ячейку, сначала нажмите клавишу ВВОД, чтобы завершить внесение изменений в активную ячейку.
Копирование значений ячеек, форматов ячеек и только формулПри вставке скопированных данных можно выполнить любое из следующих действий:
вставить только параметры форматирования ячейки, такие как цвет текста или цвет заливки (но не содержимое ячеек);
преобразовать формулы в ячейках в вычисленные значения без переопределения существующего форматирования;
вставить только формулы (но не вычисленные значения).
Выделите ячейку или диапазон ячеек со значениями, форматами или формулами, которые нужно скопировать.
На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.
Выделите левую верхнюю ячейку области вставки или ту ячейку, куда нужно вставить значение, формат ячейки или формулу.
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выполните одно из следующих действий:
чтобы вставить только значения, выберите вариант значения;
чтобы вставить только форматы ячеек, выберите вариант Форматирование;
чтобы вставить только формулы, выберите вариант формулы.
Примечание: Если копируемые формулы содержат относительные ссылки на ячейки, Excel настраивает ссылки (и относительные части смешанных ссылок) в скопированных формулах. Допустим, ячейка B8 содержит формулу =СУММ(B1:B7). Если скопировать эту формулу в ячейку C8, новая формула будет ссылаться на соответствующие ячейки этого столбца: =СУММ(C1:C7). Если копируемые формулы содержат абсолютные ссылки на ячейки, ссылки в скопированных формулах не меняются. Если вы не получили ожидаемого результата, попробуйте изменить ссылки в исходных формулах на относительные или абсолютные, а затем снова скопировать ячейки.
При вставке скопированных данных к ним применяются параметры ширины целевых ячеек. Чтобы изменить ширину ячеек в соответствии с параметрами исходных ячеек, выполните указанные ниже действия.
Выделите ячейки, которые нужно переместить или скопировать.
На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.
Чтобы переместить ячейки, нажмите кнопку Вырезать .
Сочетание клавиш Можно также нажать клавиши CTRL+X.
Чтобы скопировать ячейки, нажмите кнопку Копировать .
Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.
Выделите левую верхнюю ячейку области вставки.
Совет: Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.
На вкладке Главная в группе Буфер обмена нажмите стрелку под кнопкой Вставить и выберите пункт Сохранить ширину столбцов оригинала.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Тем, кто постоянно работает с Microsoft Excel, часто приходится задаваться вопросом правильного копирования данных таблицы с сохранением форматирования, формул или их значений.
Как вставить формулу в таблицу Excel и сохранить формат таблицы? Ведь при решении данной задачи можно экономить вагон времени. Для этого будем использовать функцию «Специальная вставка» – это простой инструмент, который позволяет быстро решить сложные задачи и освоить общие принципы его использования. Использование этого простого инструмента позволяет вам быстро приводить все таблицы к однообразному формату и получать тот результат, который необходим.
Как скопировать таблицу с шириной столбцов и высотой строк
Допустим, у нас есть такая таблица, формат которой необходимо сохранить при копировании:
При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:
Поэтому приходится вручную «расширять» ее, чтобы привести в удобный вид. Если таблица большая, то «возиться» придется долго. Но есть способ существенно сократить временные потери.
Способ1:Используем специальную вставку
- Выделяем исходную таблицу, которую нам необходимо скопировать, нажимаем на Ctrl+C.
- Выделяем новую (уже скопированную) таблицу, куда нам необходимо формат ширины столбцов и нажимаем на ячейку правой кнопкой мыши, после чего в выпадающем меню находим раздел «Специальная вставка».
- Выбираем в нем необходимый пункт напротив опции «ширина столбцов», нажимаем «ОК».
Она получила исходные параметры и выглядит идеально точно.
Способ 2: Выделяем столбцы перед копированием
Секрет данного способа в том, что если перед копированием таблицы выделить ее столбцы вместе с заголовками, то при вставке ширина каждого столбца будет так же скопирована.
Для каждого случая рационально применять свой способ. Но стоит отметить, что второй способ позволяет нам не только быстро переносить таблицу вместе с форматом, но и копировать высоту строк. Ведь в меню специальной вставки нет опции «высоту строк». Поэтому для решения такой задачи следует действовать так:
Полезный совет! Самый быстрый способ скопировать сложную и большую таблицу, сохранив ее ширину столбцов и высоту строк – это копировать ее целым листом. О том, как это сделать читайте: копирование и перемещение листов.
Вставка значений формул сохраняя формат таблицы
Специальная вставка хоть и не идеальна, все же не стоит недооценивать ее возможности. Например, как вставить значение формулы в таблицу Excel и сохранить формат ячеек.
Чтобы решить такую задачу следует выполнить 2 операции, используя специальную вставку в Excel.
Так как скопированный диапазон у нас еще находится в буфере обмена после копирования, то мы сразу еще раз вызываем специальную вставку где выбираем опцию «форматы». Жмем ОК.
Мы вставили значения формул в таблицу и сохранили форматы ячеек. Как вы догадались можно сделать и третью операцию для копирования ширины столбцов, как описано выше.
Полезный совет! Чтобы не выполнять вторую операцию можно воспользоваться инструментом «формат по образцу».
Microsoft Excel предоставляет пользователям практически неограниченные возможности для подсчета простейших функций и выполнения ряда других процедур. Использование программы позволяет устанавливать форматы, сохранять значения ячеек, работать с формулами, переносить и изменять их, таким образом, как это удобно для пользователей.
Читайте также: