Макрос для копирования листа в Excel c любым количеством копий

Создание, копирование, перемещение и удаление рабочих листов Excel с помощью кода VBA. Методы Sheets.Add, Worksheet.Copy, Worksheet.Move и Worksheet.Delete.

Синтаксис

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

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

Создание новых листов

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

‘Создание рабочего листа:

Sheets.Add

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 Type:=xlChart

‘Добавление нового листа перед

‘последним листом рабочей книги

Sheets.Add Before:=Worksheets(Worksheets.Count)

‘Добавление нового листа в конец

Sheets.Add After:=Worksheets(Worksheets.Count)

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

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

DimmyList AsObject

‘В активной книге

SetmyList=Worksheets.Add

‘В книге «Книга1.xlsm»

SetmyList=Workbooks(“Книга1.xlsm”).Worksheets.Add

‘Работаем с переменной

myList.Name=“Listok1”

myList.Cells(1,1)=myList.Name

‘Очищаем переменную

SetmyList=Nothing

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

8 ответов

кто-то на Ozgrid ответил на аналогичный вопрос. В принципе, вы просто копируете каждый лист по одному из Workbook1 в Workbook2.

Sub CopyWorkbook() Dim currentSheet as Worksheet Dim sheetIndex as Integer sheetIndex = 1 For Each currentSheet in Worksheets Windows(“SOURCE WORKBOOK”).Activate currentSheet.Select currentSheet.Copy Before:=Workbooks(“TARGET WORKBOOK”).Sheets(sheetIndex) sheetIndex = sheetIndex + 1 Next currentSheetEnd Sub

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

Я хотел бы немного переписать ответ keytarhero:

Sub CopyWorkbook()Dim sh as Worksheet, wb as workbookSet wb = workbooks(“Target workbook”)For Each sh in workbooks(“source workbook”).Worksheets sh.Copy After:=wb.Sheets(wb.sheets.count) Next shEnd Sub

Edit: вы также можете создать массив имен листов и скопировать его сразу.

Workbooks(“source workbook”).Worksheets(Array(“sheet1″,”sheet2”)).Copy _ After:=wb.Sheets(wb.sheets.count)

45

автор: Patrick Honorez

вы можете сохранить xlsx. Затем вы потеряете макросы и создадите новую книгу с немного меньшей работой.

ThisWorkbook.saveas Filename:=NewFileNameWithPath, Format:=xlOpenXMLWorkbook

я смог скопировать все листы в книге, в которой было запущено приложение vba, в новую книгу без макросов приложения с помощью:

ActiveWorkbook.Sheets.Copy

4

автор: George Ziniewicz

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

попробуйте это вместо этого.

Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheets ws.CopyNext

вы можете просто написать

Worksheets.Copy

вместо запуска цикла.По умолчанию коллекция листов воспроизводится в новой книге.

доказаны, что действует в версии 2010 XL.

Workbooks.Open Filename:=”Path(Ex: C:ReportsClientWiseReport.xls)”ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet

2 ответа

Лучший ответ

Попробуйте это и посмотрите, работает ли это. Он скопирует лист 2 в ThisWorkbook и вставит его после листа 1 в ActiveWorkbook

Option ExplicitPublic Sub copy_sheet() Dim source_worksheet As Worksheet Set source_worksheet = ThisWorkbook.Worksheets(“Sheet2”) Dim target_worksheet As Worksheet Set target_worksheet = ActiveWorkbook.Worksheets(“Sheet1”) source_worksheet.Copy After:=target_worksheetEnd Sub

4

AlwaysData 7 Апр 2017 в 03:12

Вам не нужно всю эту переменную затемнения и назначения:

Sub workBooks() ThisWorkbook.Sheet2.Copy After:=ActiveWorkbook.Sheets(7)End Sub

2

user3598756 6 Апр 2017 в 22:21

Как макросом скопировать листы в Excel

Допустим необходимо приготовить планы работ для сотрудников вашего отдела. Иметься шаблон таблицы для заполнения документа плана в виде одного рабочего листа Excel:

Шаблон плана.

Но вам необходимо создать 12 планов и соответственно 12 листов. В программе Excel нет встроенного инструмента для многократного создания копий рабочих листов за одну операцию. А копировать и вставлять 12 (а в практике встречаются случаи что и 120) листов вручную, да еще их все нужно переименовать – это потребует много рабочего времени и пользовательских сил. Определенно лучше в таком случае воспользоваться собственным макросом. А чтобы его написать воспользуйтесь VBA-кодом, который будет представлен ниже в данной статье.

Сначала откройте редактор макросов Visual Basic:

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

NextElseMsgBox “Неправильно указано количество”

End IfEnd Sub

код копирования.

Теперь если нам нужно скопировать 12 (или любое другое количество) раз листов содержащие шаблон для заполнения плана работы сотрудника, кликните по исходному листу, чтобы сделать его активным и выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«CopyList»-«Выполнить». Сразу после запуска макроса появиться диалоговое окно, в котором следует указать количество копий листа:

окно количество.

Введите, например, число 12 и нажмите ОК:

Листы скопированы 12 раз.

Лист с шаблоном плана скопируется 12 раз, а все названия листов будут иметь такое же как название исходного листа только со своим порядковым номером от 1 до12.

Внимание! Если название исходного листа слишком длинное, тогда может возникнуть ошибка в процессе выполнения макроса. Ведь в Excel название листа не может содержать более чем 31 символ. То есть ориентируйтесь так чтобы название исходного листа было меньше чем 27 символов. Так же ошибка может возникнуть если текущая рабочая книга Excel уже содержит листы с таким названием как у копий. Ведь в Excel все листы должны иметь уникальные названия.

Примечание. В новых версиях Excel (начиная от 2010 версии) одна рабочая книга может содержать максимальное количество листов, которое ограничивается лишь размером свободной оперативной памяти системы.



Требования к именам листов

К именам листов рабочей книги предъявляется несколько ограничений:

1) длина введенного имени не должна превышать 31-го знака;

2) имя листа не должно содержать ни одного из следующих знаков: двоеточи” ( : ), косая черта ( / ), вопросительный знак ( ? ), звездочка ( * ) и квадратные скобки ( [ ] );

3) имя не должно быть пустым.

Как макрос работает

В этом макросе, активный лист сначала копируется. Затем мы используем параметры, чтобы отправить копию в новую книгу, которая создается на лету. Скопированный лист позиционируется, как первый лист в новую книгу.

Используется объект ThisWorkbook. Это гарантирует, что активный лист, который копируется из рабочей книги, в которой находится код, а не из новой созданной рабочей книги.

Как переименовать лист рабочей книги при помощи мыши?

Для переименования листа необходимо:

1) в окне открытой книги дважды щелкнуть левой кнопкой мыши на ярлычке нужного листа;

2) набрать нужное имя листа, соблюдая требования к листам, изложенные выше;

3) нажать клавишу Enter на клавиатуре для закрепления введенного имени.

Вставка нового листа в Excel

  1. Чтобы вставить новый рабочий лист, найдите и нажмите кнопку Новый лист.pereimenovanie-vstavka-udalenie-lista-excel-4.png
  2. Откроется новый пустой лист.pereimenovanie-vstavka-udalenie-lista-excel-5.png

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

pereimenovanie-vstavka-udalenie-lista-excel-6.png

Пример

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

Worksheets(“Sheet1”).Copy After:=Worksheets(“Sheet3”)

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

Worksheets(“Sheet1”).CopyWith ActiveWorkbook .SaveAs Filename:=Environ(“TEMP”) & “New1.xlsx”, FileFormat:=xlOpenXMLWorkbook .Close SaveChanges:=FalseEnd With

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

Worksheets(Array(“Sheet1”, “Sheet2”, “Sheet4”)).CopyWith ActiveWorkbook .SaveAs Filename:=Environ(“TEMP”) & “New3.xlsx”, FileFormat:=xlOpenXMLWorkbook .Close SaveChanges:=False End With

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

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

Как использовать

Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:

  1. Активируйте редактор Visual Basic, нажав ALT + F11.
  2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
  3. Выберите Insert➜Module.
  4. Введите или вставьте код во вновь созданном модуле.

Перемещение/копирование листов в Excel

ikonka-MS-excel.png

Перемещать листы в Excel с помощью мышки очень просто: возьмите ярлычок листа и, двигаясь по горизонтали, перетащите его на новое место.

Если во время этой операции вы нажмете и будете удерживать клавишу [Ctrl], то лист… правильно, скопируется! (Вспомните, как мы копировали файлы в WINDOWS ). То же самое можно делать с группой листов.

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

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

Задание для самостоятельной работы:

В книге Мои таблицы. xls переместите лист Население Земли в конец книги, верните его на прежнее место, сделайте копию листа в этой же книге, скопируйте его в новую книгу.

Создайте пустой файл Мои таблицы-копии.xls ( для этого новую рабочую книгу сохраните под этим названием в каталоге C:ST). Скопируйте в него лист Население Земли.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...