Копировать лист excel в другой лист макрос

You can easily copy sheets in Excel manually with a few simple mouse clicks. On the other hand, you need a macro if you want to automate this process. In this guide, we’re going to show you how to copy sheets in Excel with VBA.

Download Workbook

Before you start

If you are new to VBA and macro concept, VBA is a programming language for Office products. Microsoft allows users to automate tasks or modify properties of Office software. A macro, on the other hand, is a set of VBA code which you tell the machine what needs to be done.

Macros, or codes, should be written in modules, which are text areas in VBA’s dedicated user interface. Also, the file should be saved as Excel Macro Enabled Workbook in XLSM format to keep the codes.

You can find detailed instructions in our How to create a macro in Excel guide.

New Workbook

Copy active sheet to a new workbook

The first code is the simplest and shortest one which performs the action the title suggests:

Public Sub CopyActiveSheetToNewWorkbook()

  ActiveSheet.Copy

End Sub

As you can figure out ActiveSheet selector indicates the active sheet in the user window. Once the code run successfully, you will see the copy in a new workbook.

Copy a specific sheet to a new workbook

The following code copies “SUMIFS” sheet into a new workbook, regardless of sheet’s active status.

Public Sub CopySpecificSheetToNewWorkbook()

  Sheets("SUMIFS").Copy

End Sub

Copy selected sheets to a new workbook

If you need to copy selected sheets into a new workbook, use ActiveWindow.SelectedSheets selector.

Public Sub CopyActiveSheetsToNewWorkbook()

  ActiveWindow.SelectedSheets.Copy

End Sub

Copy active sheet to a specific position in the same workbook

If you specify a position in the code, VBA duplicates the sheet in a specific position of in the workbook. To do this placement, you can use Before and After arguments with Copy command. With these arguments, you can place the new sheet before or after an existing worksheet.

You can use either sheet names or their indexes to indicate the existing sheet. Here are a few samples:

Public Sub CopyActiveSheetAfterSheet_Name()

  'Copies the active sheet after "Types" sheet

  ActiveSheet.Copy After:=Sheets("Types")

End Sub

    

Public Sub CopyActiveSheetAfterSheet_Index()

  'Copies after 2nd sheet

  ActiveSheet.Copy After:=Sheets(2)

End Sub

    

Public Sub CopyActiveSheetAfterLastSheet()

  'Copies the active sheet after the last sheet

  'Sheets.Count command returns the number of the sheets in the workbook

  ActiveSheet.Copy After:=Sheets(Sheets.Count)

End Sub

    

Public Sub CopyActiveSheetBeforeSheet_Name()

  'Copies the active sheet before "Types" sheet

  ActiveSheet.Copy Before:=Sheets("Types")

End Sub

    

Public Sub CopyActiveSheetBeforeSheet_Index()

  'Copies the active sheet before 2nd sheet

  ActiveSheet.Copy Before:=Sheets(2)

End Sub

    

Public Sub CopyActiveSheetBeforeFirstSheet()

  'Copies the active sheet before the first sheet

  ActiveSheet.Copy Before:=Sheets(1)

End Sub

Copy active sheet to an existing workbook

To copy anything to an existing workbook, there are 2 perquisites:

1. Target workbook should be open as well
2. You need to specify the target workbooks by name

Sub CopySpecificSheetToExistingWorkbook()

  ' define a workbook variable and assign target workbook

  ' thus, we can use variable multiple times instead of workbook reference

  Dim targetSheet As Workbook

  Set targetSheet = Workbooks("Target Workbook.xlsx")

  'copies "Names" sheet to the last position in the target workbook

  Sheets("Names").Copy After:=targetSheet.Sheets(targetSheet.Worksheets.Count)

End Sub

Возможности макросов в 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 переменные:

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

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

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

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

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

‘ActiveSheet.Name = list.Name & i

Читайте также: Макрос для переименования листов Excel при копировании.

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

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

Использование функции Dir

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

Вот там код данной функции, который выводит на лист 1 перечень файлов.

 '************************************************************** ' Sub : ExampleOne ' Author : Алексей Желтов ' Date : 15.06.2020 ' Purpose : Вывод всех файлов в папке на лист '************************************************************** Sub ExampleOne() Dim Sh As Worksheet Dim Folder As String Dim FileName As String Dim i As Long Set Sh = ThisWorkbook.Sheets(1) Folder = Sh.Cells(3, 2) ' Проверка корректности введенных данных If PathExists(Folder) = False Then MsgBox "Указанной папки не существует", 16, "Ошибка исходных данных" Exit Sub End If ' Удаляем содержимое Sh.Rows("7:" & Sh.Range("A7").End(xlDown).Row).Delete Shift:=xlUp i = 7 FileName = Dir(Folder & "/", vbNormal) Do While FileName <> "" Sh.Cells(i, 1) = i - 6 Sh.Cells(i, 2) = FileName i = i + 1 ' переход к следующему файлу FileName = Dir Loop End Sub

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

 '************************************************************** ' Function : PathExists ' Author : Алексей Желтов ' Date : 15.06.2020 ' Purpose : Возвращает ИСТИНА если путь pname существует '************************************************************** Private Function PathExists(pname As String) As Boolean On Error Resume Next If Dir(pname, vbDirectory) = "" Then PathExists = False Else PathExists = (GetAttr(pname) And vbDirectory) = vbDirectory End If End Function

Вот так выглядит результат работы функции. Программа записала список файлов на текущий лист Excel.

Здесь мы не делали проверку на тип файла и вывели все файлы которые у нас были.

Если же необходимо отобрать только определенные типы файлов, например Excel файлы, то в нашем коде необходимо сделать дополнительную проверку:

 Do While FileName <> "" If LCase(FileName) Like "*xls*" Then Sh.Cells(i, 1) = i - 6 Sh.Cells(i, 2) = FileName i = i + 1 End If ' переход к следующему файлу FileName = Dir Loop

Знак “*” означает любой набор символов. Таким образом, мы учли различные версии файлов Excel (xls, xlsx, xlsm).

Условие задачи по копированию данных

На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:

Исходная таблица задания №1

Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):

Часть результирующего списка задания №1

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

Создание новых рабочих листов осуществляется с помощью метода 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 в After:=Лист1 – это уникальное имя листа, указанное в проводнике редактора VBA без скобок.
• Лист1 в After:=Worksheets(«Лист1») – это имя на ярлыке листа, указанное в проводнике редактора VBA в скобках.

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

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

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

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

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

Создайте в нем стандартный модуль с помощью опций меню: «Insert»-«Module» и введите в него этот код, который ниже представленный на листинге:

SubCopyList()
DimkolvoAs Variant
DimiAs Long
DimlistAsWorksheet
kolvo = InputBox("Укажите необходимое количество копий для данного листа")
Ifkolvo =""Then Exit Sub
IfIsNumeric(kolvo)Then
kolvo = Fix(kolvo)
Setlist = ActiveSheet
Fori = 1Tokolvo
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 и записывает ваши точные шаги, но может вам не подойти, когда вам нужно сделать что-то большее.

• Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
• Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
• Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
• Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).

Что такое макрос?

Решение с использованием массивов

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

Подпрограммы Kopirovanie и Vstavka используются в цикле For... Next процедуры Resheniye2 по два раза, поэтому их коды вынесены за пределы процедуры Resheniye2 и вызываются по мере необходимости.

Переменные:

• massiv1 – его элементам присваиваются значения ячеек исходной таблицы;
• massiv2 – одномерный массив, заполняемый данными из переменной txt1;
• massiv3 – двумерный массив, заполняемый данными из одномерного массива massiv2 и используемый для вставки очередной строки на второй лист;
• txt1 – сюда копируются через разделитель значения элементов массива massiv1, предназначенные для заполнения очередной строки на втором листе;
• n1 – количество строк в исходной таблице;
• n2 – количество столбцов в исходной таблице;
• n3 – номер текущей строки на втором листе;
• n4 – количество столбцов текущей строки на втором листе (соответствует количеству элементов массива massiv2);
• i1, i2, i3 – счетчики цикла For… Next
• gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.

 Sub ВводТекста() ' ' ВводТекста Макрос ' ' Range("A2").Select ActiveCell.FormulaR1C1 = "Excel" Range("A3").Select End Sub 

 '************************************************************** ' Sub : ExampleTwo ' Author : Алексей Желтов ' Date : 15.06.2020 ' Purpose : Вывод всех файлов в папке на лист '************************************************************** Sub ExampleTwo() Dim Sh As Worksheet Dim FSO As New FileSystemObject Dim FolderPath As String Dim MyFolder As Folder Dim iFile As File Dim i As Long Set Sh = ThisWorkbook.Sheets(2) FolderPath = Sh.Cells(3, 2) ' Проверка корректности введенных данных If Not FSO.FolderExists(FolderPath) Then MsgBox "Указанной папки не существует", 16, "Ошибка исходных данных" Exit Sub End If ' Удаляем содержимое Sh.Rows("7:" & Sh.Range("A7").End(xlDown).Row).Delete Shift:=xlUp Set MyFolder = FSO.GetFolder(FolderPath) i = 7 For Each iFile In MyFolder.Files Sh.Cells(i, 1) = i - 6 Sh.Cells(i, 2) = iFile.Name Sh.Cells(i, 3) = iFile.Type Sh.Cells(i, 4) = iFile.DateCreated Sh.Cells(i, 5) = iFile.Size i = i + 1 Next End Sub

 Dim Sh As Worksheet Dim FSO As New FileSystemObject ' объявляем и создаем новый экземпляр объекта Dim FolderPath As String Dim MyFolder As Folder Dim iFile As File Dim i As Long

 For Each iFile In MyFolder.Files Sh.Cells(i, 1) = i - 6 Sh.Cells(i, 2) = iFile.Name ' название файла Sh.Cells(i, 3) = iFile.Type ' тип файла Sh.Cells(i, 4) = iFile.DateCreated ' дата создания Sh.Cells(i, 5) = iFile.Size ' размер i = i + 1 Next