Excel макрос копирование листа без формул

Обновлено: 06.01.2025

Установка, настройка и работа в пакете Microsoft Office.

Администратор раздела: Megaloman (Мастер-Эксперт)

Как проще всего сохранить xlsx без формул? (Excel 2007+)

Кроме специальной вставки.

Здравствуйте, Александр Романов [Alexo]!
Если нужно сохранить именно в xlsx - то некоторые варианты, которые ранее были в специальной вставке, в Excel2007 вынесены и в меню. В том числе вставка значений.
К сожалению не могу вставить скриншот, но я думаю это не сложно. Как Вы можете сделать.
Вы выделяете нужный фрагмент листа таблицы, копируете его. Затем на закладке меню "Главная" самая левая большая иконка вставки. Кликаете мышкой не на саму иконку, а на надпись "Вставить" и в подменю выбираете "Вставить значения". При этом на листе формулы заменяются на их значения. Файл сохраняете и все.
Если же не нужен именно xlsx - тогда можно посмотреть в сторону например PDF. Т.е. выбираете "Сохранить как"-->"PDF или XPX".

babylon_5

Посетитель

21.11.2009, 13:11

К сожалению эта та же самая спец. вставка, но в немного более удобном виде.
Хотелось бы иметь возможность редактирования (отсюда PDF не подходит).

Андреенков Владимир

Самый простой вариант создать в книге новый Лист, выделить все на исходном листе (Ctrl+A), копировать (Ctrl+C), на новом листе в левом углу правой кнопкой, Специальная вставка, выбрать Значения, Ок, потом еще раз в левом углу правой кнопкой, Специальная вставка, выбрать Форматы, Ок.

=====
Правильно заданный вопрос должен содержать ответ!

Александр Романов [Alexo]

как я уже написал в вопросе:

Андреенков Владимир

Извините, недосмотрел, но тем не менне, считаю, это единственный вариант.

=====
Правильно заданный вопрос должен содержать ответ!

Александр Романов [Alexo]

Я просто думал может я чего не знаю.
Сейчас приходиться сохранять исходный файл потом спец. вставкой все копировать в новую книгу.

Да, почти единственным вариантом остается копирование и вставка значений.
Еще принципиально другой путь - написать небольшой макрос, который будет проходить по диапазону ячеек и заменять формулы на значения.

Цитата: Александр Романов [Alexo]
Сейчас приходиться сохранять исходный файл потом спец. вставкой все копировать в новую книгу.

А зачем так? Если нужен другой файл со значениями, то проще: "Сохранить как", а потом "Копировать"-"Вставить значения". Намного проще (IMHO).

Александр Романов [Alexo]

Если ошибусь останусь без формул :)

Андреенков Владимир

Правильно говорит babylon_5, сначала Сохранить как. деалаем копию, а потом, записываем макрос, выделить все, копировать, специальная вставка значения. специальная вставка формулы. Это позволит потом только нажимать горячие кнопки запуска макроса, что намного быстрее и удобнее.

=====
Правильно заданный вопрос должен содержать ответ!

Андреенков Владимир

Александр Романов [Alexo]:
Я надеюсь Вы поняли, что выше описаный способ подразумевают открытой только одну книгу не надо копировать с другой + процесс ускоряется с помощью макроса, единственно, что вам предется делать, это резервная копия исходного файла

=====
Правильно заданный вопрос должен содержать ответ!

Александр Романов [Alexo]

Black Cloud, а как бы я макрос запустил из одной для другой?)

Андреенков Владимир

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

=====
Правильно заданный вопрос должен содержать ответ!

Александр Романов [Alexo]

записываем макрос, выделить все, копировать, специальная вставка значения.

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

а как бы я макрос запустил из одной для другой?)

Вопрос был про то как я макросом скопирую данные из другой книги? фактически смаил в конце означал, что то типа зачем это уточнение если такое в прицепе невозможно?

Со всей ответственностью могу сказать:
а) сделать макросом резервную копию можно;
б) макрос из одной книги может работать с другой (в т.ч. макросом можно сначала и создать эту другую книгу, или выбрать ее в диалоге "Открыть файл", или просто использовать заданное имя файла для открытия);
в) макросом можно читать значения в одной книге/листе и записывать их в другую книгу/лист;
г) макросом можно устанавливать любое оформление ячеек/областей;
и т.д. и т.п. тут ограничений практически нет. Можно даже в реестре макросом поковыряться. :)

Андреенков Владимир

Вопрос был про то как я макросом скопирую данные из другой книги? фактически смаил в конце означал, что то типа зачем это уточнение если такое в прицепе невозможно?

Вы по-моему не поняли, проведите эксперимент, откройте книгу, выделить все, копировать, теперь на этом же листе Специальная вставка значение, Специальная вставка форматы, все. Я не понимаю зачем что-то копровать из другой книги? Надо просто сделать копию оригинала и рабоать с ним, ведь вопрос стоял в том как избавиться от формул, это решение, просто данные не надо переносить из другой книги и можно ускорить процесс избавления от формул за счет использования макроса.

=====
Правильно заданный вопрос должен содержать ответ!

Андреенков Владимир

babylon_5:
Целиком поддерживаю, просто я смотрю человек не сильно разбирается в макросах, не стал нагружать как работать и с другими книгами с помощью них, тем более без этого можноо обойтись, все решается в пределах одной книги достаточно сделать копию оригинала.

=====
Правильно заданный вопрос должен содержать ответ!

Александр Романов [Alexo]

Я не понимаю зачем что-то копровать из другой книги?

вот и я не понимаю зачем вы об этом упомянули.

babylon_5, как же макрос работает с книгами если заранее не известно как будет называться книга из которой надо будет скопировать содержимое ячеек?

Андреенков Владимир

Вы забываете то, что пишите, вот Ваши слова:

Вопрос был про то как я макросом скопирую данные из другой книги?

а теперь Вы спрашиваете зачем я упомянул о копировании из другой книги, но ведь Вы сами об этом спрашивали!? А я только спросил зачем копировать из другой книги, если можно работать с одной книгой (копией оригинала).

=====
Правильно заданный вопрос должен содержать ответ!

Цитата: Александр Романов [Alexo]
как же макрос работает с книгами если заранее не известно как будет называться книга из которой надо будет скопировать содержимое ячеек?

б) макрос из одной книги может работать с другой (в т.ч. макросом можно сначала и создать эту другую книгу, или выбрать ее в диалоге "Открыть файл", или просто использовать заданное имя файла для открытия);

Но, если что еще раз структурно:
а) содержимое ячеек может копироваться из текущей книги в книгу с заранее заданным именем;
б) книга может быть выбрана в диалоге (есть, есть в VBA команды выбора файла (давайте сменим термин, уж очень далеко ушло развитие макроязыка со времен MS Office 6 и теперь это уже мощный язык программирования Visual Basic for Application (VBA)));
в) есть еще вариант создать книгу, заполнить ее данными и записать (с заданным именем или с запросом имени - возможны оба варианта). но это уже для случая не "из которой надо будет скопировать" а "в которую".
А само имя, собственно, аккуратненько в переменную поместим и до поры-до времени будем сохранять.

И вообще, по-моему мы уже пошли по кругу.
Не буду спорить с ТопикСтартером, и не буду портить его картину мира. Не может ему макрос работать с другими книгами - значит в его мире это не возможно. Тем более, что есть глубокие сложности с именами этих книг.
В моем мире макросы прекрасно соглашались работать с другими книгами когда мне это от них требовалось. Ну, значит мой мир именно таков. Мне видимо проще.

Андреенков Владимир

Еще раз повторю, потому что я не понял что Вам непонятно:

сначала, делаете, копию листа внутри книги оригинала, записываете, макрос,

выделить все, копировать, теперь на этом же листе Специальная вставка значение, Специальная вставка форматы, все.

удаляете лишний лист, сохраняете, теперь в оригинале книги есть макрос

Для того чтобы получить книгу без формул открываете оригинал, Сохранить как. сохраняете под другим именем, запускаем макрос, получаем книгу без формул.

=====
Правильно заданный вопрос должен содержать ответ!

Андреенков Владимир

=====
Правильно заданный вопрос должен содержать ответ!

Black Cloud , Бакалавр
Да ну, усложнение. Если уж макрос - так он сам и сделает новую книгу (копию текущей. ), и например, символ какой к имени исходной книги для новой добавит. Если уж пользовать макрос - зачем лишние телодвижения? Пусть он и работает. А нам останется только его запускать.

Андреенков Владимир

=====
Правильно заданный вопрос должен содержать ответ!

Александр Романов [Alexo]

Что ж из вас никто в ответе то макрос не написал? А только писали про то что я и так уже знаю, а?)

А то так получается что вы про макросы и сами не знали пока не погуглили.

Андреенков Владимир

Александр Романов [Alexo]:
Для того чтобы написать макрос так как я сказал, необходимо выполнить: Вид-Макросы-Запись макроса, указать имя макроса, выбрать "горячую клавишу", ок (ecxel 2007). Выполнить необходимые действия: выделить все, копировать, теперь на этом же листе Специальная вставка значение, Специальная вставка форматы. После получения нужного результата: Вид-Макросы-Остановить запись. Теперь макрос будет запускаться "горячей клавишей", как видите никаких дополнительных знаний не требуется и смысла писать Вам текст этого макроса тоже нет, т.к. Вы сами в состоянии его создать, так как Вам нужно. Если хотите еще и с помощью макроса делать копию документа, то Вам придется изучать язык макросов, для начала справки офиса будет достаточно.

=====
Правильно заданный вопрос должен содержать ответ!

Александр Романов [Alexo]

Вопрос был не в этом.

И как писать макросы я знаю. Про макросы я сразу и не подумал, так как думал что есть возможность сохранить книгу просто без формул.

Никто из вас кстати тоже про макросы не подумал, так как все сразу начали писать про спец. вставку о которой я упомянул в вопросе.

Андреенков Владимир

Сначала Вы говорите:

Что ж из вас никто в ответе то макрос не написал?

Непонятно тогда зачем Вам нужен макрос, если сами умеете?
В результате описанного мною способа все, что Вам нужно сделать, это нажать Сохранить как. и нажать "горячую клавишу" запуска макроса. Если Вы считаете, что это будет все равно недостаточно быстрое выполнение Вашей задачи, то извините.

=====
Правильно заданный вопрос должен содержать ответ!

Цитата: Александр Романов [Alexo]
вы про макросы и сами не знали пока не погуглили.

Цитата: Александр Романов [Alexo]
Как проще всего сохранить xlsx без формул?

Какой вопрос задали, такой ответ и получили. Мы тут мысли читать не умеем (простите засранцев). Что спрашивают - на то и отвечаем. Вот именно, не в этом. И макросы - это совсем не самое простое. Я потому и упомянул про них в мини-форуме, что это не в вопрос (кстати просто по приколу, а за это зацепились - оказалось очень даже в тему ).
Вот если бы Вы, сэр, в вопросе написали бы хотя бы как в комментарии:

Цитата: Александр Романов [Alexo]
Я просто думал может я чего не знаю.
Сейчас приходиться сохранять исходный файл потом спец. вставкой все копировать в новую книгу.

Т.е. про спец.вставку знаю, хочу узнать какие пути еще вообще есть. Вы бы получили совсем другие ответы.
А так: вы просили как попроще в 2007м это сделать, я Вам и написал где ткнуть чтобы попроще. Да, это корнями где-то из спец.вставки, но сильно проще.
Вот где-то так.

2 Black Cloud , Бакалавр
ТС сам писать макросы не умеет. Он только "знает как". А это, как говорят в Одессе, две большие разницы.
Ну, так, чтобы было понятно даже мне (а не только ТС-у): знать как заниматься сексом и заниматься им - это вот. тут тоже есть в ньюансах различие.

Все. В этом вопросе я больше не пишу, потому как флудэрство уже пошло, а это не есть гуд. И карму не улучшит. Воооотт.

Александр Романов [Alexo]

Ответ всего один и он не ваш. я не имею ввиду форум.

И макрос я имел ввиду полный макрос, т.е. исходник VBA скрипта который сам выделит все, скопирует, создаст новую книгу и туда все запишет. Вобщем полный он и в Африке полный.

ТС сам писать макросы не умеет. Он только "знает как". А это, как говорят в Одессе, две большие разницы.
Ну, так, чтобы было понятно даже мне (а не только ТС-у): знать как заниматься сексом и заниматься им - это вот. тут тоже есть в ньюансах различие.

Хех, как низко же вы пали.

Хм, блин. Таки напишу.

Цитата: Александр Романов [Alexo]
И макрос я имел ввиду полный макрос, т.е. исходник VBA скрипта который сам выделит все, скопирует, создаст новую книгу и туда все запишет. Вобщем полный он и в Африке полный.

Тут есть два ответа. И оба Вам не понравятся.
1. Ну, тут про ключи от квартиры. (надеюсь ясно)
2. Полный макрос - лЁгко. Правда, поскольку вот по такой информации:
Как проще всего сохранить xlsx без формул? (Excel 2007+)

Кроме специальной вставки.
его написать сложновато, я предлагаю Вам такой вариант:
- стучитесь ко мне в почту/аську/скайп
- я Вам дам номер WM-кошелька
- с Вас 10WMZ и подробно что, с какого листа, в каком диапазоне ячеек, в каком виде перекинуть в новую книгу или из старой книги. а еще лучше образец файла
- с меня макрос, который (если захотите) спросит из какой книги будем брать данные, перенесет все, спросит имя книги под которым сохранять новую (или сформирует это имя по Вашим правилам) и сделает это усё как в лучших домах лондОна и пАрижа. А за +10WMZ он обретет лист с настройками, где Вы сможетеоперативно переключать способы открытия/записи и менять префикс/суффикс имени нового файла.
Вобщем будет полный как в Африке.
Хоть уже лет шесть не писал под Ёксель, но ради такого случая. грех не вспомнить. Да и не проблема.
Засим разрешите откланяться.

Александр Романов [Alexo]

Все. В этом вопросе я больше не пишу, потому как флудэрство уже пошло, а это не есть гуд. И карму не улучшит. Воооотт.

его написать сложновато, я предлагаю Вам такой вариант:

Я попозже сам напишу, как время найдется. Заодно и гляну насколько это сложно.

Александр Романов [Alexo]

Вот, очень простой скрипт. (Тут есть еще над чем работать)
Без знаний VB/VBA написал. Просто немного переделав макрос созданный екселем. Не думаю что этот способ можно считать более сложным чем Спец. вставка.

Есть код который копирует активный лист (КНИГИ А) в новую книгу ( например КНИГА Б) и берет название для книги из ячейки , НО тк данные для активного листа( КНИГА А) берутся из другого листа(КНИГА А) , то и в новой книге (КНИГА Б) они так же зависят от этого листа(КНИГА А)
Я добавлял в код замену формул на символы, но так сохранение происходит очень долго.
Есть ли из этого выход?

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Сохранение отдельного листа книги в отдельный файл
Здравствуйте форумчане. нужна ваша помощь. Есть книга. В книге два листа. нужно второй.

Макрос сохранение активного листа книги в отдельный файл
Код сохраняет файл, но с пустым содержанием. Подскажите, в чем может быть проблема? With.

Сохранение листа в из книги как отдельный файл.xls
Процедура по нажатию кнопки-сохранить к примеру лист3 из текущей книги как отдельный файл.xls с.

Сохранение листа книги в отдельный файл в альбомном виде
Добрый день! Есть данный макрос, он сохраняет лист из книги в отдельный файл, но не совсем так как.

Нужно, чтобы в новой КНИГЕ Б не было никаких привязок к КНИГЕ А.

Замена формул на значения не устраивает т.к процесс сохранения и замены занимает много времени

Нужно, чтобы в новой КНИГЕ Б не было никаких привязок к КНИГЕ А.
Замена формул на значения не устраивает т.к процесс сохранения и замены занимает много времени

EXCEL MONSTER, из выше сказанного не ясно, каким образом вы делали замену формул, делали ли вы замену всех формул или только избранных, что такое много времени в этом случае, устроит ли вас новая книга в которой не будет не только никаких привязок к другой книге, но и никаких формул, только значения.

Если вы делали замену пробегом по ячейкам в цикле, то способ побыстрее - скопировать сразу весь диапазон листа и сохранить его как значения (метод PasteSpecial). Проблема в том, что в этом случае будут все формулы заменены значениями. Если вас такой итог не устраивает, придётся жертвовать временем.

Копирует лист в другое расположение в текущей книге или новой книге.

Синтаксис

выражения. Copy (Before, After)

выражение Переменная, представляюная объект "Таблица".

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Before	Необязательный	Variant	Лист, перед которым будет размещен скопирован лист. Вы не можете указать Перед, если указать После.
After	Необязательный	Variant	Лист, после которого будет размещен скопирован лист. Вы не можете указать После, если вы укажите Before.

Примечания

Если вы не указываете ни до, ни после, Microsoft Excel создает новую книгу, которая содержит скопированные объекты таблицы. Недавно созданная книга содержит свойство Application.ActiveWorkbook и содержит одну таблицу. В единой таблице сохраняются свойства name и CodeName исходных таблиц. Если скопированная таблица держала лист кода листа в проекте VBA, то это также осуществляется в новой книге.

Выбор массива из нескольких листов можно скопировать на новый пустой объект книги аналогичным образом.

Источник и назначение должны быть в одном Excel. Пример приложения, в противном случае он вызывает ошибку времени запуска '1004': Нет такого интерфейса поддерживается, если что-то подобное было использовано, или ошибка времени запуска Sheet1.Copy objWb.Sheets(1) '1004': Метод копирования класса Worksheet не удалось, если что-то подобное ThisWorkbook.Worksheets("Sheet1").Copy objWb.Sheets(1) было использовано.

Пример

В этом примере копируется Sheet1, размещая копию после Sheet3.

В этом примере сначала скопируется sheet1 в новую пустую книгу, а затем сохраняется и закрывается новая книга.

В этом примере листы Sheet1, Sheet2 и Sheet4 копируется в новую пустую книгу, а затем сохраняет и закрывает новую книгу.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Возможности макросов в Excel практически неограниченные. В данном примере покажем в пару кликов можно создать любое количество копий листов используя 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

В коде используются 3 переменные:

kolvo – в этой переменной определено какое количество копий будет создано при копировании текущего рабочего листа Excel.
i – счетчик циклов.
list – в этой переменной будет создан экземпляр объекта листа Excel.

В начале макроса вызываем диалоговое окно, в котором пользователь должен указать в поле ввода какое количество копий листов необходимо создать с помощью данного макроса «CopyList». Введенное числовое значение в поле ввода из этого диалогового окна передается в переменную kolvo. Если поле ввода пустое или в диалоговом окне была нажата кнопка отмены «Cancel», тогда дальнейшие инструкции не выполняться и работа макроса прерывается.

В следующей строке кода проверяется: является ли введенное значение в поле ввода – числовым? Если да, тогда на всякий случай удаляются все числа после запятой с помощью функции Fix.

Далее в переменой list создается экземпляр объекта ActiveSheet. После в цикле копируются листы. Количество циклов выполняется ровно столько, сколько пользователь указал в диалоговом окне макроса. В процессе копирования каждый раз изменяется название для новой копии листа. Так как в одной книге не может быть 2 и более листов с одинаковым названием. Уникальные названия для каждой копии создаются за счет присвоения к названию исходного листа число с порядковым номером текущего цикла. При необходимости пользователь может задать свои параметры для присвоения названия копиям листов изменив данную строку кода. Главное придерживаться правила уникальности названий листов.

Примечание. Если нет необходимости настраивать названия для новых листов, тогда данную строку кода можно закомментировать.

'ActiveSheet.Name = list.Name & i

В таком случае названия для копий Excel будет присваивать сам. Например, для исходного листа с названием «Лист1» копии будут получать названия: «Лист1 (2)», «Лист1 (3)», «Лист1 (4)» и т.д.

Создание новых рабочих листов осуществляется с помощью метода 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, который для создания диаграмм уже не подойдет.

Примеры создания листов

ThisWorkbook . Sheets . Add After : = ActiveSheet , Count : = 2 Workbooks ( "Книга1.xlsm" ) . Sheets . Add After : = Лист 1 Workbooks ( "Книга1.xlsm" ) . Sheets . Add After : = Worksheets ( 1 ) Workbooks ( "Книга1.xlsm" ) . Sheets . Add After : = Worksheets ( "Лист1" ) Workbooks ( "Книга1.xlsm" ) . Sheets . Add . Name = "Мой новый лист" Sheets . Add Before : = Worksheets ( Worksheets . Count )

Лист1 в After:=Лист1 — это уникальное имя листа, указанное в проводнике редактора VBA без скобок.
Лист1 в After:=Worksheets(«Лист1») — это имя на ярлыке листа, указанное в проводнике редактора VBA в скобках.

Создаваемый лист можно присвоить объектной переменной:

Set myList = Workbooks ( "Книга1.xlsm" ) . Worksheets . Add

Если создаваемый лист присваивается объектной переменной, он будет помещен перед активным листом. Указать дополнительные параметры невозможно.

Копирование листов

Копирование рабочих листов осуществляется с помощью метода Worksheet.Copy.

Синтаксис метода Worksheet.Copy

expression.Copy [Before, After]

где expression — переменная, представляющая собой объект Worksheet.

Компоненты метода Worksheet.Copy

Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет добавлена копия.
After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет добавлена копия.

*Если Before и After не указаны, Excel создаст новую книгу и поместит копию листа в нее. Если скопированный лист содержит код в проекте VBA (в модуле листа), он тоже будет перенесен в новую книгу.

Примеры копирования листов

Worksheets ( "Лист1" ) . Copy Before : = Worksheets ( "Лист2" ) Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Copy _

Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.

Перемещение листов

Перемещение рабочих листов осуществляется с помощью метода Worksheet.Move.

Синтаксис метода Worksheet.Move

expression.Move [Before, After]

где expression — переменная, представляющая собой объект Worksheet.

Компоненты метода Worksheet.Move

Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет размещен перемещаемый лист.
After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет размещен перемещаемый лист.

*Если Before и After не указаны, Excel создаст новую книгу и переместит лист в нее.

Примеры перемещения листов

Простые примеры перемещения листов:

Worksheets ( "Лист1" ) . Move Before : = Worksheets ( "Лист2" ) Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Move _

Перемещение листа «Лист4» в позицию перед листом, указанным как по порядковому номеру, так и по имени ярлыка:

x = InputBox ( "Введите имя или номер листа" , "Перемещение листа «Лист4»" )

Удаление листов

Удаление рабочих листов осуществляется с помощью метода Worksheet.Delete

Синтаксис метода Worksheet.Delete

где expression — переменная, представляющая собой объект Worksheet.

Примеры удаления листов

Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Delete

Как обратиться к рабочему листу, переименовать, скрыть или отобразить его с помощью кода VBA Excel, смотрите в этой статье.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

48 комментариев для “VBA Excel. Рабочий лист (создание, копирование, удаление)”

А как удалить листы не указывая постоянно их название?:

Если по крупному
нет Проверки наличия Листа в книге
нет Переименования Листа

Добрый день, Эдуард!
Я правильно понял вопрос: нужно, чтобы макрос при каждом запуске создавал копию листа и присваивал ему новое имя, отличное от имени по умолчанию? Если так, то по каким правилам должно создаваться новое имя?

Здравствуйте. Не могу найти макрос может кто поможет.
Задача состоит в том что бы макрос читал все листы книги после чего делал копию этих листов и вставлял в эти же листы данные как значения. Т.е. фактически разрывал связи методом копи паст но на всех листах книги (колличество листов в книге может быть каждый раз разное.

Добрый день!
У меня вопрос.
Как перемещать лист перед другим листом, номер или название которого будет вводить пользователь?

Привет, Артур!
Перемещение листа «Лист2» на позицию перед листом, имя ярлыка которого вводится в текстовое поле информационного окна InputBox:

x = InputBox ( "Введите имя листа" , "Перемещение листа «Лист2»" )

Спасибо!
У меня ещё вопрос. А если указывать номер листа, перед которым будет другой лист, какое изменение будет в коде?

А так можно вводить и номер, и имя:

x = InputBox ( "Введите имя или номер листа" , "Перемещение листа «Лист2»" ) x = InputBox ( "Введите имя или номер листа" , "Перемещение листа «Лист2»" )

Добрый день! Возможно ли полное копирование листа на нужный мне лист? В январе заполняются данные каждый день и надо чтобы за каждый день создавалась копия на заранее созданные 31 лист по дням?

. PasteSpecial Paste : = xlPasteFormulasAndNumberFormats

Добрый день, Эдуард!
Это можно сделать двумя строками, но перед копированием следует проверить, не был ли исходный лист сегодня уже сохранен под новым именем:

MsgBox "Лист за " & FormatDateTime ( Date , vbShortDate ) & " уже сохранен!" Worksheets ( "Січень" ) . Copy After : = Worksheets ( Worksheets . Count ) 'присваиваем листу в качестве имени число текущей даты

Спасибо за помощь! Нашел другое решение более мне подходящее но не знаю как сделать чтобы созданые листы вставлялись по порядку нумерации 1,2,3 и т.д. после активного листа? Не поможете вот код

destSht . Move After : = Worksheets ( Worksheets . Count )

Спасибо все работает!

Еще вопрос, можно ли скопировать лист не в конец книги а между листами, есть 12 месяцев в книге ексель чтобы копировалось после января, февраля?

И может еще чтобы кнопка с макросом не копировалась а создавалась гипперссылка обратно на лист?

Sheets . Add . Name = InputBox ( "Ввести дату - від 1 до 31" ) destSht . Move After : = Worksheets ( Worksheets . Count )

Я добавил код перемещения нового листа относительно исходного листа с именем текущего месяца и удаления ненужного нового листа, который создается при повторном вводе одного и того же числа:

Sheets . Add . Name = InputBox ( "Ввести дату - від 1 до 31" ) destSht . Move After : = Worksheets ( CStr ( CByte ( destSht . Name ) - 1 ) )

Евгений огромное спасибо это то что и требовалось весьма признателен за Вашу помощь.

Эдуард, представленный выше код будет работать только с одним месяцем. Excel не даст создать в одной книге несколько листов с одним именем (одно и то же число, но разных месяцев). Вам придется как-то изменить наименование листов, например: 1.01-31.01, 1.02-28.02. Тогда для перемещения новых листов придется использовать другой алгоритм или по окончании одного месяца, все его листы переименовать, например так:

MsgBox "Такой номер уже используется - введите другой"

Я понял этого и не требуется по окончанию месяца листы будут сохранены отдельно.
А вот по предедущему коду нельзя ввести произвольное число т.е не по порядку 1,2,3 а сразу например 17 пропустив с 1 по 16?

Sheets . Add . Name = InputBox ( "Ввести дату - від 1 до 31" )

Помогите с мелочью никак не пойму куда вставить умножить на 2,1 и на 1,9 чтобы не считалось +_ 20% в этой формуле?
=ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1))

Эдуард, может быть у вас для ячейки с формулой установлен формат «Процентный»?

Нет числовой. условное форматирование со стрелками вверх вниз и вправо
=ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1*2,6)) для стрелки вверх
=ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1*2,4)) для стрелки вправо
но не работает может я не туда вставляю *2,6

Синтаксис функции АДРЕС:
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[A1];[имя_листа])
Там, где вы умножаете, должно быть логическое значение [A1]: ИСТИНА или ЛОЖЬ (1 или 0).

удаляю Лист по имени на ярлычке

Евгений, помогите пожалуйста со следующей задачей.
В папке несколько документов эксель, в каждом из которых несколько листов.
Необходимо, чтобы макрос открывал по порядку каждый документ, проходил по каждому листу и брал оттуда нужную информацию. Затем эта информация переносится в новый документ эксель в заданном порядке.
В целом программу я реализовал, но столкнулся с проблемой, что debug упирается в последний лист первого открытого документа и не может дальше перейти к следующему документу. Т.е. необходимо прописать условие, чтобы vba понимал, что данный лист в документе последний.
Подскажите пожалуйста, как это сделать?

Цикл будет завершен после обработки последнего листа.

Здравствуйте!
Возможно ли при копировании листа из другого файла перенести и пользовательский стиль таблицы?

Добрый день!
Если не ошибаюсь, пользовательский стиль таблицы всегда копируется вместе с листом, но внешний вид таблиц будет одинаков, если оба файла созданы и открыты в одной версии Excel.

Спасибо за ответ.
Увы, не так, и даже всё не так.
Стиль «пытается» копироваться, цвета отличаются оттенками, но это полбеды. Главное, что нужно мне, чтобы копировался цвет шрифта таблицы, а этого не происходит. Почему-то на листе-цели устанавливается на весь лист чёрный шрифт, который Эксель считает главнее табличного.
И даже больше. После ручной замены на всё листе-цели шрифта с чёрного на авто замены цвета шрифта в импортированной таблице не происходит. Что всегда есть при ручной работе с таблицами.
Ну, и совсем глюк. Пользовательский стиль переезжает и доступен в новой книге. Но даже при ручном его выборе на листе-цели для таблицы не происходит смены цвета шрифта.
Цвет можно поменять только руками (макросом) без учёта таблицы.
Всё это происходит на одном компьютере в одной ОС В10.

Хм. Эксель 2016, всё выше описанное по-прежнему.
Если не затруднит, скопируйте умную таблицу макросом на новый лист, перед этим созданный тем же макросом.

Читайте также: