Макрос VBA копирует не те данные, пропускает строки, ломает формат или вставляет всё не туда? Разбираем 9 частых ошибок и показываем, как правильно копировать данные в Excel через VBA.
Почему макрос в Excel работает медленно — 7 ошибок, которые тормозят VBA
Кнопка «Изменить» неактивна в Excel — 5 причин и точные решения
Макрос не запускается в Excel — 7 причин и решения
Макрос должен был просто скопировать данные.
Обычная задача: взять строки из одной таблицы, перенести на другой лист, собрать отчёт, вставить значения без лишнего мусора. На словах всё просто. На практике Excel вдруг копирует не туда, теряет формат, вставляет пустые строки, захватывает лишние столбцы или вообще молча делает вид, что всё нормально.
Самое неприятное здесь не сама ошибка. Самое неприятное — ты не сразу понимаешь, где она возникла. Таблица вроде заполнена, макрос вроде отработал, ошибок на экране нет. А потом открываешь результат и видишь: данные съехали, часть строк пропала, формулы превратились в значения, а в отчёте уже каша.
И вот тут начинается ручная проверка. Строка за строкой. Лист за листом. То самое офисное болото, где человек уже не работает с Excel, а спасает то, что Excel натворил вместе с макросом. Хотя виноват почти всегда не Excel. Виноват код, который не учитывает несколько важных деталей.
Сегодня разберём 9 ошибок VBA, из-за которых макрос копирует данные неправильно. Не в теории, а на реальных ситуациях: когда копируется лишнее, когда не копируется нужное, когда формат ломается, когда код работает «через раз» и когда всё выглядит правильно, но результат уже испорчен.
Если ты работаешь с отчётами, выгрузками, таблицами клиентов, остатками, заявками, актами, счетами или любыми повторяющимися Excel-файлами — эта статья сэкономит тебе много нервов. Потому что копирование данных через VBA кажется простой задачей ровно до первого испорченного отчёта.
Почему копирование через VBA часто ломается
На первый взгляд команда копирования в VBA выглядит безобидно. Взял диапазон, скопировал, вставил. Но в Excel диапазон — это не просто прямоугольник с данными. Внутри него могут быть формулы, скрытые строки, фильтры, объединённые ячейки, пустые области, форматирование, примечания, ссылки и старые остатки от прошлых действий.
Если макрос написан слишком «в лоб», он не различает, что именно нужно перенести. Он просто выполняет команду. А команда Copy в Excel часто копирует не только значения, но и всё окружение: формат, ширину, формулы, границы, скрытые элементы и иногда даже то, что пользователь не хотел трогать.
Из-за этого появляется классическая проблема: макрос технически работает, но результат получается неправильный. Ошибки нет, окно не выскочило, выполнение завершилось. Но данные уже вставлены не так, как нужно. Для человека это хуже обычной ошибки, потому что обычная ошибка хотя бы останавливает процесс. А такая ошибка тихо портит результат.
Именно поэтому хороший макрос для копирования данных должен не просто «копировать». Он должен понимать, что брать, куда вставлять, в каком виде переносить, как найти последнюю строку, как не захватить пустоты и как не перезаписать уже существующие данные.
Ошибка №1. Макрос копирует весь лист вместо нужного диапазона
Одна из самых частых ошибок — копировать слишком большой диапазон. Пользователь хотел перенести таблицу, а макрос забирает весь лист или огромную область, где часть ячеек кажется пустой, но на самом деле содержит старое форматирование. В итоге файл становится тяжелее, вставка занимает больше времени, а на новом листе появляются лишние пустые строки и столбцы.
Это часто случается, когда используется UsedRange. Команда выглядит удобно, но в реальной работе она может подвести. Excel считает использованными не только ячейки с данными, но и ячейки, где когда-то было форматирование, старые значения или следы удалённых данных. Пользователь их уже не видит, а Excel всё ещё помнит.
Например, на листе когда-то была таблица до строки 50000. Потом данные удалили, оставили только 300 строк, но UsedRange всё равно может захватывать старую область. Макрос честно копирует «использованный диапазон», а ты потом удивляешься, почему вставка занимает вечность и отчёт раздувается.
Правильнее явно определять последнюю строку и последний столбец по реальным данным. Тогда макрос копирует именно таблицу, а не всё, что Excel когда-то посчитал использованным.
Dim lastRow As Long
Dim lastCol As Long
lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, lastCol)).Copy
Здесь логика простая: последняя строка ищется по первому столбцу, последний столбец — по первой строке. Это уже намного безопаснее, чем копировать весь лист. Но и тут есть нюанс: если в первом столбце бывают пустые значения, последняя строка может определиться неправильно. Тогда нужно искать последнюю строку по более надёжному столбцу или по всему листу.
Для отчётов лучше заранее выбрать опорный столбец. Например, если в таблице всегда есть номер заявки, дата, артикул или имя клиента, именно по нему и нужно искать конец данных. Не по случайному столбцу, не по первому попавшемуся, а по тому, где пропусков быть не должно.
Ошибка №2. Макрос вставляет данные поверх старых строк
Это больная классика. Макрос копирует данные с одного листа на другой, но вместо добавления в конец вставляет их поверх старой информации. В результате часть отчёта пропадает, старые строки заменяются новыми, а пользователь понимает это слишком поздно.
Обычно проблема в том, что место вставки указано жёстко. Например, всегда A2. Для первого запуска это работает. Для второго — уже нет. Если на листе уже есть данные, новый блок просто перекроет старый. Excel не будет спрашивать разрешения. VBA выполняет то, что написано.
Надёжный подход — каждый раз искать следующую свободную строку на листе назначения. Тогда макрос не перезаписывает данные, а добавляет новый блок ниже.
Dim nextRow As Long
nextRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row + 1
wsTarget.Cells(nextRow, 1).PasteSpecial Paste:=xlPasteValues
Этот фрагмент ищет последнюю заполненную строку в первом столбце листа назначения и вставляет данные ниже. Но здесь тоже нужно быть внимательным. Если на листе есть только заголовки, следующая строка должна быть второй. Если лист полностью пустой, End(xlUp) может вернуть первую строку, и код поведёт себя не так, как ожидалось.
Поэтому в серьёзных макросах лучше добавлять проверку: есть ли данные на листе, не является ли последняя строка заголовком, нужно ли вставлять с первой строки или со второй. Это кажется мелочью, но именно такие мелочи отделяют рабочий макрос от макроса, который однажды испортит отчёт.
Ошибка №3. Копируются формулы вместо значений
Иногда пользователь хочет перенести готовый результат, а макрос переносит формулы. На новом листе формулы начинают ссылаться не туда, показывают ошибки, подтягивают старые данные или вообще превращаются в набор непонятных ссылок.
Это особенно часто происходит с отчётами, где исходная таблица содержит расчёты. Например, в одной книге формула работает правильно, потому что ссылается на соседние листы. А после копирования на другой лист или в другую книгу она начинает искать источники, которых там нет.
Если цель — перенести именно результат, нужно вставлять значения, а не всё содержимое ячейки.
wsSource.Range("A1:D100").Copy
wsTarget.Range("A1").PasteSpecial Paste:=xlPasteValues
Такой вариант переносит только значения. Формулы останутся в исходной таблице, а на новом листе появятся готовые числа и текст. Для отчётов это обычно безопаснее.
Но бывает обратная задача: нужно сохранить и формулы, и формат. Тогда PasteValues не подходит. Поэтому перед написанием макроса нужно чётко решить, что именно переносится: значения, формулы, формат или всё вместе. Если этого решения нет, макрос рано или поздно начнёт делать не то.
Хорошая практика — в названии процедуры сразу указывать смысл. Например, CopyValuesToReport, CopyFormulasToTemplate, CopyFullTable. Это не просто красота. Через месяц ты сам быстрее поймёшь, что делает код, и не будешь гадать, почему он вставляет именно значения.
Ошибка №4. Формат ломается после вставки
Иногда данные перенеслись правильно, но внешний вид таблицы развалился. Ширина столбцов другая, даты отображаются как числа, денежные значения теряют формат, проценты становятся обычными десятичными числами, границы исчезают. Вроде данные на месте, но пользоваться отчётом неудобно.
Причина часто в том, что макрос вставляет только значения. Это хорошо для защиты от сломанных формул, но плохо, если формат тоже важен. В таких случаях нужно отдельно переносить значения и отдельно применять оформление. Не надеяться, что Excel сам догадается.
Например, можно сначала вставить значения, а потом скопировать формат:
wsSource.Range("A1:D100").Copy
wsTarget.Range("A1").PasteSpecial Paste:=xlPasteValues
wsSource.Range("A1:D100").Copy
wsTarget.Range("A1").PasteSpecial Paste:=xlPasteFormats
Такой подход позволяет получить результат без формул, но с внешним видом исходной таблицы. Для отчётов, которые отправляются руководителю, клиенту или коллегам, это часто лучший вариант.
Но есть важный момент: если слепо копировать формат из старой таблицы, можно перенести и лишнее оформление. Например, подсветку ошибок, старые цвета, границы, которые уже не нужны. Поэтому для регулярных отчётов лучше иметь отдельный шаблон оформления, а макрос должен вставлять в него только данные.
Это особенно удобно, если отчёт выходит каждый день или каждую неделю. Один раз создаёшь красивый шаблон, а дальше VBA просто заполняет его свежими данными. Тогда не нужно каждый раз бороться с форматами.
Ошибка №5. Макрос не учитывает фильтры и скрытые строки
Очень коварная ошибка: на листе включён фильтр, пользователь видит только нужные строки и думает, что макрос скопирует именно их. Но код копирует весь диапазон, включая скрытые строки. В результате в отчёт попадает то, что пользователь специально отфильтровал.
Это одна из тех ошибок, которые трудно заметить сразу. Визуально на экране всё выглядит правильно: лишних строк не видно. Но VBA может работать с полным диапазоном, если явно не указать, что нужны только видимые ячейки.
Если нужно копировать только отфильтрованные строки, следует использовать SpecialCells.
wsSource.Range("A1:D100").SpecialCells(xlCellTypeVisible).Copy
wsTarget.Range("A1").PasteSpecial Paste:=xlPasteValues
Этот код копирует только видимые ячейки. Но и здесь есть ловушка. Если после фильтрации не осталось видимых строк, SpecialCells может вызвать ошибку. Поэтому такой код лучше оборачивать в обработку ошибок.
On Error Resume Next
wsSource.Range("A1:D100").SpecialCells(xlCellTypeVisible).Copy
If Err.Number <> 0 Then
MsgBox "Нет видимых строк для копирования"*
Err.Clear*
End If
On Error GoTo 0
В идеале лучше не использовать On Error Resume Next надолго. Он удобен, но опасен, если оставить его включённым на большой участок кода. Макрос начнёт пропускать ошибки, и ты не поймёшь, что именно пошло не так.
Работая с фильтрами, всегда нужно задавать себе вопрос: макрос должен копировать всё или только то, что сейчас видно на экране? Это разные задачи, и код для них должен быть разным.
Полный VBA-набор, сценарии, готовые макросы, проверка результата, журнал и резервная копия. Скачать файл
Ошибка №6. Неправильно определяется последняя строка
Большая часть ошибок с копированием начинается с одной строки кода: определения последней строки. Если она определена неправильно, дальше всё идёт криво. Макрос может скопировать не весь диапазон, пропустить нижние строки или, наоборот, захватить лишние пустые области.
Самый популярный вариант:
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Он работает, если первый столбец всегда заполнен. Но если в первом столбце бывают пропуски, итог будет неверным. Например, таблица реально идёт до строки 500, но в столбце A данные заканчиваются на строке 240. Макрос решит, что таблица закончилась, и не скопирует оставшуюся часть.
Поэтому нужно выбирать столбец, который действительно надёжен. Если в таблице есть обязательное поле, например ID, дата, номер документа или артикул, используем его.
lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
Если же надёжного столбца нет, можно искать последнюю заполненную ячейку по всему листу:
Dim lastCell As Range
Set lastCell = wsSource.Cells.Find(What:="", After:=wsSource.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)*
If Not lastCell Is Nothing Then lastRow = lastCell.Row
Этот вариант сложнее, но часто надёжнее. Он ищет последнюю ячейку с любым содержимым. Так можно избежать ситуации, когда один пустой столбец ломает весь макрос.
Правда, и здесь нужно помнить: если на листе есть случайные заметки далеко внизу, Find найдёт их тоже. Поэтому идеального универсального способа нет. Есть только правильный способ под конкретную структуру таблицы.
Ошибка №7. Макрос использует активный лист вместо нужного
Очень опасная ошибка — работа с ActiveSheet. Пока ты запускаешь макрос сам и всё контролируешь, он может работать нормально. Но стоит случайно открыть другой лист, кликнуть не туда или запустить процедуру из другой книги — и код начнёт копировать данные не оттуда.
Это одна из причин, почему макросы иногда «работают через раз». На самом деле они работают строго по коду. Просто код привязан не к конкретному листу, а к тому, что сейчас активно.
Плохой вариант:
Range("A1:D100").Copy
Здесь не указано, с какого листа копировать. VBA возьмёт активный лист. Если активен не тот лист, результат будет неправильный.
Правильнее явно указывать объекты:
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Set wsSource = ThisWorkbook.Worksheets("Данные")
Set wsTarget = ThisWorkbook.Worksheets("Отчёт")
wsSource.Range("A1:D100").Copy
wsTarget.Range("A1").PasteSpecial Paste:=xlPasteValues
Такой код не зависит от того, какой лист открыт на экране. Он всегда берёт данные с листа «Данные» и вставляет на лист «Отчёт». Это намного надёжнее.
Если макрос работает с несколькими книгами, нужно так же явно указывать Workbook. Иначе можно случайно вставить данные не в тот файл. Особенно если открыто несколько Excel-документов одновременно.
Ошибка №8. Макрос не очищает старый результат
Иногда макрос вставляет новые данные поверх старых, но старые строки ниже остаются. Например, вчера в отчёте было 500 строк, сегодня только 320. Макрос вставил новые 320 строк, а строки с 321 по 500 остались от прошлого отчёта. Пользователь видит лишние данные и может принять их за актуальные.
Это очень опасно для отчётов. Особенно если данные идут в расчёты, сводные таблицы или отправляются дальше. Старые строки могут попасть в итоговые суммы, и ошибка станет финансовой, а не просто визуальной.
Если отчёт должен обновляться полностью, перед вставкой нужно очищать область результата.
wsTarget.Range("A2:D100000").ClearContents
Но очищать нужно аккуратно. Если на листе есть формулы, заголовки, оформление или служебные блоки, нельзя чистить всё подряд. Лучше заранее определить область, где живут только данные.
Иногда полезно чистить не только содержимое, но и формат:
wsTarget.Range("A2:D100000").Clear
Но Clear удаляет всё: значения, формулы, формат, примечания. Поэтому использовать его нужно только там, где это действительно безопасно.
Для шаблонных отчётов часто лучше очищать только значения:
wsTarget.Range("A2:D100000").ClearContents
Так оформление остаётся, а данные обновляются. Это аккуратнее и быстрее.
Ошибка №9. Нет проверки результата после копирования
Многие макросы делают копирование и сразу завершаются. Пользователь видит сообщение «Готово», но макрос не проверяет, сколько строк перенесено, есть ли пустые значения, совпадает ли количество строк до и после, не возникла ли ошибка при вставке.
Такой макрос похож на курьера, который бросил коробку у двери и убежал. Формально доставил. Что внутри — неизвестно.
Хороший макрос должен хотя бы минимально проверять результат. Например, посчитать количество строк в исходной таблице и количество вставленных строк. Если числа сильно отличаются, показать предупреждение.
Dim sourceRows As Long
Dim targetRows As Long
sourceRows = lastRow - 1
targetRows = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row - 1
MsgBox "Скопировано строк: " & sourceRows
Это простая проверка, но она уже снижает риск. Пользователь видит, сколько строк обработано. Если ожидалось 800, а макрос пишет 120, проблема заметна сразу.
В более серьёзных макросах можно делать отдельный лист журнала: дата запуска, имя пользователя, количество строк, время выполнения, статус. Это особенно полезно, если макросом пользуются несколько человек. Тогда не нужно гадать, кто запускал файл и что произошло.
Готовый безопасный шаблон копирования данных
Теперь соберём более правильный вариант. Это не универсальная магия на все случаи, но хороший базовый шаблон для копирования данных с одного листа на другой. Он явно указывает листы, ищет последнюю строку, очищает старый результат, вставляет значения и возвращает настройки Excel.
Sub CopyDataSafely()
Dim wsSource As Worksheet*
Dim wsTarget As Worksheet*
Dim lastRow As Long*
Dim lastCol As Long*
Dim sourceRange As Range*
Dim targetCell As Range*
On Error GoTo ErrHandler*
Application.ScreenUpdating = False*
Application.EnableEvents = False*
Application.Calculation = xlCalculationManual*
Set wsSource = ThisWorkbook.Worksheets("Данные")*
Set wsTarget = ThisWorkbook.Worksheets("Отчёт")*
lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row*
lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column*
If lastRow < 2 Then*
MsgBox "На листе Данные нет строк для копирования"*
GoTo SafeExit*
End If*
Set sourceRange = wsSource.Range(wsSource.Cells(2, 1), wsSource.Cells(lastRow, lastCol))*
wsTarget.Range("A2:Z100000").ClearContents*
Set targetCell = wsTarget.Range("A2")*
targetCell.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value*
MsgBox "Готово. Скопировано строк: " & sourceRange.Rows.Count*
SafeExit:
Application.ScreenUpdating = True*
Application.EnableEvents = True*
Application.Calculation = xlCalculationAutomatic*
Exit Sub*
ErrHandler:
MsgBox "Ошибка: " & Err.Description*
Resume SafeExit*
End Sub
Обрати внимание: здесь нет Copy и PasteSpecial. Данные переносятся напрямую через Value. Это быстрее и чище. Такой способ хорош, когда нужно перенести именно значения без формата. Если нужен формат, его можно добавить отдельно, но для большинства отчётов перенос значений через массивный диапазон работает надёжнее.
Ещё один важный момент: в шаблоне очищается диапазон A2:Z100000. В реальном файле его нужно подстроить под свою таблицу. Если у тебя больше столбцов, расширь диапазон. Если меньше, не очищай лишнее. Макрос должен быть точным, как хороший слесарный ключ: не болтаться и не срывать грани.
Когда лучше не использовать Copy вообще
Многие привыкли к команде Copy, потому что она повторяет обычное действие пользователя. Но для VBA это не всегда лучший вариант. Copy включает буфер обмена, зависит от состояния Excel и часто переносит больше, чем нужно.
Если задача — перенести значения, лучше использовать прямое присваивание:
wsTarget.Range("A2").Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value
Это быстрее, надёжнее и не трогает буфер обмена. После такого кода не остаётся «бегущей рамки» вокруг скопированного диапазона, не возникает конфликтов с буфером и макрос работает спокойнее.
Copy стоит использовать тогда, когда нужно перенести формат, формулы, ширину столбцов или полный внешний вид. Но если нужен только результат, прямое присваивание почти всегда лучше.
Как понять, что макрос копирует правильно
Есть простой тест. После выполнения макроса ты должен ответить на пять вопросов.
Первое: совпадает ли количество строк в источнике и результате. Второе: не появились ли лишние пустые строки. Третье: не остались ли старые данные ниже новой таблицы. Четвёртое: не превратились ли даты, суммы и проценты в неправильный формат. Пятое: не скопировались ли скрытые строки, которые не должны были попасть в отчёт.
Если хотя бы по одному пункту есть сомнение, макрос нужно доработать. Не потом. Не когда-нибудь. Сразу. Потому что одна тихая ошибка в копировании может испортить не только таблицу, но и решение, которое примут на основе этой таблицы.
Именно поэтому макросы для копирования данных должны быть скучными в хорошем смысле. Они не должны удивлять. Они должны каждый раз делать одно и то же, предсказуемо и аккуратно.
Что делать, если макрос уже испортил данные
Если макрос перезаписал старые данные или вставил результат неправильно, главное — не продолжать работать поверх испорченного файла. Сначала нужно сохранить копию текущего состояния, чтобы можно было разобраться. Затем проверить исходный файл, место вставки и диапазон очистки.
Если есть резервная копия — восстановить данные проще. Если её нет, иногда помогает история версий OneDrive, Google Drive, корпоративного хранилища или автоматические копии Excel. Но надеяться на это как на систему нельзя.
Хороший макрос должен либо создавать резервную копию перед серьёзным изменением, либо работать на отдельном листе, не трогая исходные данные. Особенно если файл важный.
Можно добавить простую защиту: перед очисткой отчёта спросить подтверждение.
If MsgBox("Очистить старые данные и вставить новые?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Для ежедневных автоматических процессов это может мешать, но для ручного запуска такая проверка иногда спасает от ошибки.
Практический вывод
Копирование данных через VBA — это не просто команда Copy. Это целая цепочка решений: что копировать, откуда брать, куда вставлять, как определить границы, что делать со старыми данными, переносить ли формат, учитывать ли фильтры и как проверить результат.
Если эти решения не прописаны в коде, Excel будет действовать буквально. А буквальное выполнение не всегда означает правильный результат.
Поэтому хороший макрос должен быть не просто рабочим, а безопасным. Он должен явно указывать листы, аккуратно находить диапазоны, не зависеть от активного окна, не портить старые данные и возвращать пользователю понятный результат.
В Телеграме я выкладываю готовые файлы и шаблоны, которые можно использовать без ручной сборки:
макросы для отчётов, очистки данных, копирования таблиц, ускорения VBA и защиты от типовых ошибок.
Если хочешь не просто читать разборы, а забирать рабочие инструменты — подписывайся.
Итог
Если макрос копирует данные неправильно, не нужно сразу переписывать всё с нуля. Чаще всего достаточно проверить 9 вещей: диапазон, последнюю строку, место вставки, формулы, формат, фильтры, активный лист, старые данные и проверку результата.
Проблема почти всегда находится именно там.
Excel не обязан понимать, что ты хотел сделать. Он выполняет код. А значит, задача хорошего макроса — не оставлять Excel права на догадки.
Когда код написан точно, копирование становится спокойным процессом: нажал кнопку, получил чистый отчёт, пошёл дальше. Без ручной проверки каждой строки. Без страха, что что-то съехало. Без вечернего сидения над таблицей, которая должна была собраться за минуту.