Ошибка при массированном копировании Excel листов

@Petr2005 · Регистрация: 14.05.2011

Студворк — интернет-сервис помощи студентам

Уважаемые VBA-гуру!
Подскважите плз. почему возникает следующая проблема.
Есть следующий код

Visual Basic
1
2
3
4
For i = 1 To 300
ActiveSheet.Cells(1, 1).Select
ActiveSheet.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next i

На первый взгляд этот код работает без проблем. Но если на копируемый лист вставить диаграмму на 176 шаге возникает ошибка
Runtime 1004. 'Метод Copy класса worksheet завершен неверно'.
В чем проблема? И как ее обойти?
Жду в онлайне
ICQ 233918893

@pashulka · 16.05.2011, 09:25

- Откройте VBA редактор и посмотрите каких 'монстров' Вы создаёте. Имеется ввиду имя рабочего листа в среде VBA (CodeName)
Имя рабочего листа не может содержать более 256 символов, и кодовое имя, по всей видимости имеет сходные ограничения.
- Вы можете просто создавать новые рабочие листы и копировать содержимое исходного листа. В данном примере в качестве исходного рабочего листа взят первый.

Visual Basic
1
2
3
4
5
6
Set s = ThisWorkbook.Worksheets
 
For i = 1 To 300
    s.Add after:=s(s.Count)
    s(1).Cells.Copy s(s.Count).Cells
Next

P.S. А если человек не гуру, значит ему и ответить уж нельзя.

@Petr2005 · 17.05.2011, 09:07 **[ТС]**

Уважаемый pashulka,
Спасибо за ответ. Во первых, как 'залезть' в VBA редактор?
Во вторых, метод с копированием данных и вставкой этих данных в новый созданный лист не годится т.к. в таком случае теряется информация о форматах ячеек, размерах ячеек, общих настроек листа (например колонтитулах). И, самое главное, при использовании предложенного Вами метода придется копировать и вставлять отдельно диграммы, шейпы и прочие объекты.
Есть идеи? Зараннее спасибо за участие в обсужждении.
P.S. Каждый написавший 5-10 осмысленных строчек на VBA может считать себя гуру, и конечно же может учавствовать в данном обсуждении.

@pashulka · 17.05.2011, 20:46

Уважаемый Petr2005

Во первых, информация о форматах ячеек не теряется, т.е. большинство параметров форматирования будет скопировано. Размер ячеек в новых рабочих листах также будет адекватен первоначальному.
Во вторых, колонтитул не является частью об'екта Range поэтому его естественно нельзя скопировать. Впрочем никто не мешает создать колонтитул для каждого созданного рабочего листа.
В третьих, диаграммы, и большинство других Shapes также будут скопированы во вновь создаваемые рабочие листы.

P.S. Редактор VBA - ALT+F11

@Petr2005 · 18.05.2011, 11:14 **[ТС]**

Насчет форматов Вы правы. Но
1. создавать колонтитулы каждый раз не лучшее решение
2. При копровании диаграммы с листа на лист ссылки копирумой диаграммы на дданные будут указывать на исходный лист, а не на новый созданный лист. А это доолнительная проблема.

Вообще мне необхоодимо создать большое число однотипных отчетов-листов. Естесственно копирование листов является наиболее подходящим вариантом, копирование данных с листа на лист (как вы предлагаете) влечет множество мелких нюансов, которые придется учитывать в коде программы (Это не есть хорошо, по своему опыту знаю - число возможных ошибок и недоработок в лучшем случае пропорционально объему исходного кода).

Итак, прошу отвечать по существу. Что является причиной ошибки описанной мною изначально, и как ее избежать?

Johny Walker · 18.05.2011, 13:13

Я попробовал твой код - у меня ошибк возникает на 182 шаге. Причем при повторном запуске она возникает сразу, даже если закрыть файл без сохранения и открыть с 1 листом. Попытка скопировать лист вручную также не проходит, причем никаких ошибок не выдается - просто лист не копируется. Помогает только полное закрытие XL и повторный его запуск.
Отсюда могу сделать вывод о проблеме распределения/освобождения памяти экселем, потому что если 3 раза по 100 раз запустить процедуры, в промежутках полностью выгружая эксель и сохраняя промежуточные результаты, добиться результата можно, но это, естественно, не метод.
Интересно, а если вручную скопировать лист 300 раз - прокатит? Лень было пробовать :-)
Мой вывод - нужно искать другой путь, если не получится освободить память (ну уж если эксель сам этого не делает при закрытии файла - я бы не рыпался)
Например, можно выполнять копирование на том же самом листе, вниз, с вставкой разрыва страницы (и колонтитулы, и ссылки останутся).
А вообще очень часто при работе экселя в подобном длинном цикле возникает данный глюк, выражается в резком замедлении работы при повторном запуске той же процедуры даже после закрытия и повторного открытия файла)

@Petr2005 · 18.05.2011, 15:02 **[ТС]**

Johny Walker,
Спасибо за участие.
Описанные тобою действия я конечно же проделывал прежде чем обратиться к форуму. Вручную копировать не пробовал...-лень.
У меня так же есть подозрения на некорректное распределение памяти Excel'ем. А остепенное замедление работы я отношу к проблеме уникальности имен листов.
Но все это только домыслы, а решение проблемы так и не найдено.
Твое предложение относительно копирования с разрывом страниц на одном листе не подходит, т.к.
1. слишком длинный отчет становится не читабельным.
2. отчет слишком большой, чтобы поместиться в 65536 строк excel'я
Petr2005

Johny Walker · 18.05.2011, 15:24

Судя по всему, ты взял для своей задачи слишком слабый инструмент. Я бы на твоем месте попробовал все-таки логически разбить выходной отчет на несколько крупных блоков, и сохранять каждый в отдельной книге. Либо переходить на Access...

@Masalov · 18.05.2011, 15:45

а что написано в Help в разделе 'Технические характеристики и ограничения Microsoft Excel' ?

Johny Walker · 18.05.2011, 16:00

А то и написано - ограничивается объемом памяти. Я же говорю, Access нужен...

@Petr2005 · 18.05.2011, 17:14 **[ТС]**

Инструмент - слабый, сомнений нет! Но с точки зрения пользователя, нет ничего проще XL. Конечно можно выбрать и C++ и Oracle - тогда вообще проблем не будет

), будет одно удовольствие.
Дело в том, что не всегда есть возможность выбора инструмента.
В данном случае, есть проблема - ее надо решить, а не обойти.
Итак, есть еще предложения?

Johny Walker · 18.05.2011, 17:38

Ну, попробуй в ворд отчеты кидать - он еще проще, и умеет сливать содержимое во временные файлы...
А вырыть совковой лопатой котлован в одиночку - ненужное геройство, объясни ты заказчику...

@Petr2005 · 18.05.2011, 18:59 **[ТС]**

Ты опять предлагаешь обойти проблему, а не решить ее. Обход я нашел куда более изящный, чем ворд, и задачу свою выполнил. Тем не менее проблема, обозначенная мной, остается не решенной.

@pashulka · 19.05.2011, 04:43

Petr2005,
Если немного исправить Ваш первоначальный макрос, то можно добиться нормальных кодовых имён, а также результата, при котором исходные данные для диаграммы будут расположены в том же листе, что и сама диаграмма. И соответственно решить вопрос с колонтитулами и т.д. Но если Вам не хватает ресурсов для выполнения поставленной задачи, то можно создавать не 300 рабочих листов в одной книге, а например 10 рабочих книг, содержащих 30 одинаковых рабочих листов.

@Gleb1 · 19.05.2011, 12:41

Предлагаю макрос с решением Вашей проблемы по поводу вставки листов с диаграммой - после вставки листа надо закрывать файл:

Visual Basic
1
2
3
4
5
6
Do
Workbooks.Open 'имя файла'
N = Worksheets.Count
Worksheets(1).Copy after:=Worksheets(N)
ActiveWorkbook.Close Savechanges:=True
Loop While N < 300

Новые блоги и статьи Все статьи Все блоги /
Модель микоризы: классовый агентный подход 3 anaschu 06.01.2026 aa0a7f55b50dd51c5ec569d2d10c54f6/ O1rJuneU_ls https:/ / vkvideo. ru/ video-115721503_456239114	Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ФедосеевПавел 06.01.2026 Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ВВЕДЕНИЕ Введу сокращения: аналоговый ПИД — ПИД регулятор с управляющим выходом в виде числа в диапазоне от 0% до. . .	Модель микоризы: классовый агентный подход 2 anaschu 06.01.2026 репозиторий https:/ / github. com/ shumilovas/ fungi ветка по-частям. коммит Create переделка под биомассу. txt вход sc, но sm считается внутри мицелия. кстати, обьем тоже должен там считаться. . . .	Расчёт токов в цепи постоянного тока igorrr37 05.01.2026 / * Дана цепь постоянного тока с сопротивлениями и напряжениями. Надо найти токи в ветвях. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа и решает её. Последовательность действий:. . .
Новый CodeBlocs. Версия 25.03 palva 04.01.2026 Оказывается, недавно вышла новая версия CodeBlocks за номером 25. 03. Когда-то давно я возился с только что вышедшей тогда версией 20. 03. С тех пор я давно снёс всё с компьютера и забыл. Теперь. . .	Модель микоризы: классовый агентный подход anaschu 02.01.2026 Раньше это было два гриба и бактерия. Теперь три гриба, растение. И на уровне агентов добавится между грибами или бактериями взаимодействий. До того я пробовал подход через многомерные массивы,. . .	Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Programma_Boinc 28.12.2025 Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / ********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / ********/ gallery/ bKBkQFf Пост отсюда. . .	Кто-нибудь знает, где можно бесплатно получить настольный компьютер или ноутбук? США. Programma_Boinc 26.12.2025 Нашел на реддите интересную статью под названием Anyone know where to get a free Desktop or Laptop? Ниже её машинный перевод. После долгих разбирательств я наконец-то вернула себе. . .

@Petr2005 0 / 0 / 0 Регистрация: 14.05.2011 Сообщений: 6
	17.05.2011, 09:07 [ТС]
	Уважаемый pashulka, Спасибо за ответ. Во первых, как 'залезть' в VBA редактор? Во вторых, метод с копированием данных и вставкой этих данных в новый созданный лист не годится т.к. в таком случае теряется информация о форматах ячеек, размерах ячеек, общих настроек листа (например колонтитулах). И, самое главное, при использовании предложенного Вами метода придется копировать и вставлять отдельно диграммы, шейпы и прочие объекты. Есть идеи? Зараннее спасибо за участие в обсужждении. P.S. Каждый написавший 5-10 осмысленных строчек на VBA может считать себя гуру, и конечно же может учавствовать в данном обсуждении. 0

@pashulka 4138 / 2242 / 940 Регистрация: 01.12.2010 Сообщений: 4,624
	17.05.2011, 20:46
	Уважаемый Petr2005 Во первых, информация о форматах ячеек не теряется, т.е. большинство параметров форматирования будет скопировано. Размер ячеек в новых рабочих листах также будет адекватен первоначальному. Во вторых, колонтитул не является частью об'екта Range поэтому его естественно нельзя скопировать. Впрочем никто не мешает создать колонтитул для каждого созданного рабочего листа. В третьих, диаграммы, и большинство других Shapes также будут скопированы во вновь создаваемые рабочие листы. P.S. Редактор VBA - ALT+F11 0

@Petr2005 0 / 0 / 0 Регистрация: 14.05.2011 Сообщений: 6
	18.05.2011, 11:14 [ТС]
	Насчет форматов Вы правы. Но 1. создавать колонтитулы каждый раз не лучшее решение 2. При копровании диаграммы с листа на лист ссылки копирумой диаграммы на дданные будут указывать на исходный лист, а не на новый созданный лист. А это доолнительная проблема. Вообще мне необхоодимо создать большое число однотипных отчетов-листов. Естесственно копирование листов является наиболее подходящим вариантом, копирование данных с листа на лист (как вы предлагаете) влечет множество мелких нюансов, которые придется учитывать в коде программы (Это не есть хорошо, по своему опыту знаю - число возможных ошибок и недоработок в лучшем случае пропорционально объему исходного кода). Итак, прошу отвечать по существу. Что является причиной ошибки описанной мною изначально, и как ее избежать? 0

Johny Walker 2 / 2 / 1 Регистрация: 10.04.2011 Сообщений: 415
	18.05.2011, 13:13
	Я попробовал твой код - у меня ошибк возникает на 182 шаге. Причем при повторном запуске она возникает сразу, даже если закрыть файл без сохранения и открыть с 1 листом. Попытка скопировать лист вручную также не проходит, причем никаких ошибок не выдается - просто лист не копируется. Помогает только полное закрытие XL и повторный его запуск. Отсюда могу сделать вывод о проблеме распределения/освобождения памяти экселем, потому что если 3 раза по 100 раз запустить процедуры, в промежутках полностью выгружая эксель и сохраняя промежуточные результаты, добиться результата можно, но это, естественно, не метод. Интересно, а если вручную скопировать лист 300 раз - прокатит? Лень было пробовать :-) Мой вывод - нужно искать другой путь, если не получится освободить память (ну уж если эксель сам этого не делает при закрытии файла - я бы не рыпался) Например, можно выполнять копирование на том же самом листе, вниз, с вставкой разрыва страницы (и колонтитулы, и ссылки останутся). А вообще очень часто при работе экселя в подобном длинном цикле возникает данный глюк, выражается в резком замедлении работы при повторном запуске той же процедуры даже после закрытия и повторного открытия файла) 0

@Petr2005 0 / 0 / 0 Регистрация: 14.05.2011 Сообщений: 6
	18.05.2011, 15:02 [ТС]
	Johny Walker, Спасибо за участие. Описанные тобою действия я конечно же проделывал прежде чем обратиться к форуму. Вручную копировать не пробовал...-лень. У меня так же есть подозрения на некорректное распределение памяти Excel'ем. А остепенное замедление работы я отношу к проблеме уникальности имен листов. Но все это только домыслы, а решение проблемы так и не найдено. Твое предложение относительно копирования с разрывом страниц на одном листе не подходит, т.к. 1. слишком длинный отчет становится не читабельным. 2. отчет слишком большой, чтобы поместиться в 65536 строк excel'я Petr2005 0

Johny Walker 2 / 2 / 1 Регистрация: 10.04.2011 Сообщений: 415
	18.05.2011, 15:24
	Судя по всему, ты взял для своей задачи слишком слабый инструмент. Я бы на твоем месте попробовал все-таки логически разбить выходной отчет на несколько крупных блоков, и сохранять каждый в отдельной книге. Либо переходить на Access... 0

@Masalov 22 / 5 / 1 Регистрация: 05.09.2010 Сообщений: 370
	18.05.2011, 15:45
	а что написано в Help в разделе 'Технические характеристики и ограничения Microsoft Excel' ? 0

Johny Walker 2 / 2 / 1 Регистрация: 10.04.2011 Сообщений: 415
	18.05.2011, 16:00
	А то и написано - ограничивается объемом памяти. Я же говорю, Access нужен... 0

@Petr2005 0 / 0 / 0 Регистрация: 14.05.2011 Сообщений: 6
	18.05.2011, 17:14 [ТС]
	Инструмент - слабый, сомнений нет! Но с точки зрения пользователя, нет ничего проще XL. Конечно можно выбрать и C++ и Oracle - тогда вообще проблем не будет ), будет одно удовольствие. Дело в том, что не всегда есть возможность выбора инструмента. В данном случае, есть проблема - ее надо решить, а не обойти. Итак, есть еще предложения? 0

Johny Walker 2 / 2 / 1 Регистрация: 10.04.2011 Сообщений: 415
	18.05.2011, 17:38
	Ну, попробуй в ворд отчеты кидать - он еще проще, и умеет сливать содержимое во временные файлы... А вырыть совковой лопатой котлован в одиночку - ненужное геройство, объясни ты заказчику... 0

@Petr2005 0 / 0 / 0 Регистрация: 14.05.2011 Сообщений: 6
	18.05.2011, 18:59 [ТС]
	Ты опять предлагаешь обойти проблему, а не решить ее. Обход я нашел куда более изящный, чем ворд, и задачу свою выполнил. Тем не менее проблема, обозначенная мной, остается не решенной. 0

@pashulka 4138 / 2242 / 940 Регистрация: 01.12.2010 Сообщений: 4,624
	19.05.2011, 04:43
	Petr2005, Если немного исправить Ваш первоначальный макрос, то можно добиться нормальных кодовых имён, а также результата, при котором исходные данные для диаграммы будут расположены в том же листе, что и сама диаграмма. И соответственно решить вопрос с колонтитулами и т.д. Но если Вам не хватает ресурсов для выполнения поставленной задачи, то можно создавать не 300 рабочих листов в одной книге, а например 10 рабочих книг, содержащих 30 одинаковых рабочих листов. 0