Как продублировать лист в Excel с помощью VBA

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

Вручную копировать листы в Excel довольно быстро и просто… если делать это один или два раза. Дублирование нескольких листов несколько раз утомительно и отнимает много времени. На этой странице вы найдете несколько полезных макросов для автоматизации этой задачи.

Excel VBA для копирования листа в новую книгу

Этот простейший однострочный макрос делает именно то, что следует из его названия — копирует активный лист в новую книгу.

Public Sub CopySheetToNewWorkbook() activeSheet.Copy End Sub

Скопируйте несколько листов в Excel с помощью VBA

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

Public Sub CopySelectedSheets() ActiveWindow.SelectedSheets.Copy End Sub

Excel VBA для копирования листа в другую книгу

В зависимости от того, куда вы хотите вставить скопированный лист, используйте один из следующих макросов.

Скопировать лист в начало другой книги

Этот макрос копирует активный лист перед всеми остальными рабочими листами в целевом файле, Книга1 в этом примере. Чтобы скопировать в другой файл, замените «Book1.xlsx» полным именем целевой книги.

Public Sub CopySheetToBeginningAnotherWorkbook() activeSheet.Copy Before:=Workbooks(“Book1.xlsx”).Sheets(1) End Sub

Скопировать лист в конец другой книги

Этот фрагмент кода дублирует активный рабочий лист и помещает копию в конец Книга1. Опять же, не забудьте заменить «Book1.xlsx» на имя целевой книги.

Public Sub CopySheetToEndAnotherWorkbook() activeSheet.Copy After:=Workbooks(“Book1.xlsx”).Sheets(Workbooks(“Book1.xlsx”).Worksheets.Count) End Sub

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

Копировать лист в выбранную книгу

Чтобы иметь возможность скопировать текущий лист в любую открытую книгу, вы можете создать пользовательскую форму (с именем Пользовательская форма1) с СписокБокс контроль (с именем ListBox1) и две кнопки:

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

Public SelectedWorkbook As String Private Sub UserForm_Initialize() SelectedWorkbook = “” ListBox1.Clear For Each wbk In Application.Workbooks ListBox1.AddItem (wbk.Name) Next End Sub Private Sub CommandButton1_Click() If ListBox1.ListIndex > -1 Then SelectedWorkbook = ListBox1 .List(ListBox1.ListIndex) End If Me.Hide End Sub Private Sub CommandButton2_Click() SelectedWorkbook = “” Me.Hide End Sub

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

Копировать лист в начало выбранной книги:

Public Sub CopySheetToBeginningAnotherWorkbook() Загрузить UserForm1 UserForm1.Show If (UserForm1.SelectedWorkbook <> “”) Then activeSheet.Copy Before:=Workbooks(UserForm1.SelectedWorkbook).Sheets(1) End If Unload UserForm1 End Sub

Копировать лист в конец выбранной книги:

Public Sub CopySheetToEndAnotherWorkbook() Загрузить UserForm1 UserForm1.Show If (UserForm1.SelectedWorkbook <> “”) Then activeSheet.Copy After:=Workbooks( _ UserForm1.SelectedWorkbook).Sheets( _ Workbooks(UserForm1.SelectedWorkbook). _ Worksheets.Count) End If Unload UserForm1 End Sub

При запуске в Excel макрос покажет вам список всех открытых книг. Вы выбираете нужный и нажимаете ОК:

Макрос Excel для копирования листа и переименования

Когда вы копируете лист в Excel, реплике присваивается имя в формате по умолчанию, например Лист1 (2). Следующие макросы могут избавить вас от необходимости вручную изменять имя по умолчанию.

Этот код дублирует активный лист, называет копию «Тестовый лист» (вы можете заменить его любым другим именем) и помещает скопированный лист в конец текущей книги.

Public Sub CopySheetAndRenamePredefined() activeSheet.Copy After:=Worksheets(Sheets.Count) При ошибке Возобновить Далее activeSheet.Name = “Test Sheet” End Sub

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

Public Sub CopySheetAndRename() Dim newName As String On Error Resume Next newName = InputBox(“Введите имя скопированного рабочего листа”) If newName <> “” Then activeSheet.Copy After:=Worksheets(Sheets.Count) On Error Resume Next activeSheet.Name = newName End If End Sub

После запуска макрос отображает следующее поле ввода, в котором вы вводите желаемое имя и нажимаете OK:

Макрос Excel для копирования листа и переименования на основе значения ячейки

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

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

Public Sub CopySheetAndRenameByCell() Dim newName As String On Error Resume Next newName = InputBox(“Введите имя скопированного рабочего листа”, “Копировать рабочий лист”, ActiveCell.Value) If newName <> “” Then activeSheet.Copy After:=Worksheets (Sheets.Count) При ошибке возобновить следующий activeSheet.Name = newName End If End Sub

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

Public Sub CopySheetAndRenameByCell2() Dim wks As Worksheet Set wks = activeSheet activeSheet.Copy After:=Worksheets(Sheets.Count) If wks.Range(“A1”).Value <> “” Then On Error Resume Next activeSheet.Name = wks .Range(“A1”).Value End If wks.Activate End Sub

Макрос для копирования рабочего листа в закрытую книгу

Этот макрос копирует активный лист в конец закрытой книги. Имя другой книги в коде не указано — макрос откроет стандартное окно проводника Windows и позволит выбрать любой файл назначения:

После выбора файла и нажатия Открытыммакрос скопирует активный лист и автоматически закроет целевую книгу.

Public Sub CopySheetToClosedWorkbook() Dim fileName Dim closedBook As Workbook Dim currentSheet As Worksheet fileName = Application.GetOpenFilename(“Excel Files (*.xlsx), *.xlsx”) If fileName <> False Then Application.ScreenUpdating = False Set currentSheet = Application .activeSheet Set ClosedBook = Workbooks.Open(fileName) currentSheet.Copy After:=closedBook.Sheets(closedBook.Worksheets.Count) closedBook.Close (True) Application.ScreenUpdating = True End If End Sub

Excel VBA для копирования листа из другой книги без открытия

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

Только не забудьте сделать пару замен в коде:

• C:\Users\XXX\Documents\Target_Book.xlsx следует изменить на фактический путь и имя книги, из которой вы хотите скопировать лист.
• Sheet1 следует заменить именем листа, который вы хотите скопировать.

Public Sub CopySheetFromClosedWorkbook() Dim sourceBook As Workbook Application.ScreenUpdating = False Set sourceBook = Workbooks.Open(“C:\Users\XXX\Documents\Target_Book.xlsx”) sourceBook.Sheets(“Sheet1”).Копировать после:=ThisWorkbook .Sheets(ThisWorkbook.Sheets.Count) sourceBook.Close Application.ScreenUpdating = True End Sub

Excel VBA для дублирования листа несколько раз

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

Public Sub DuplicateSheetMultipleTimes() Dim n As Integer On Error Resume Next n = InputBox(“Сколько копий активного листа вы хотите сделать?”) Если n >= 1 Then For numtimes = 1 To n activeSheet.Copy After: =ActiveWorkbook.Sheets(Worksheets.Count) Next End If End Sub

Откройте исходный лист, запустите макрос, укажите, сколько копий активного листа вы хотите сделать, и нажмите ХОРОШО:

Как дублировать листы в Excel с помощью VBA

Чтобы скопировать лист в Excel одним из вышеперечисленных макросов, вы можете либо вставить код VBA в свою книгу, либо запустить макрос из нашего образец рабочей тетради.

Как добавить макрос в книгу

Чтобы вставить код в книгу, выполните следующие действия:

1. Откройте рабочий лист, который вы хотите скопировать.
2. Нажмите Alt + F11, чтобы открыть редактор Visual Basic.
3. На левой панели щелкните правой кнопкой мыши Эта рабочая тетрадьа затем щелкните Вставлять > Модуль.
4. Вставьте код в окно кода.
5. Нажмите F5, чтобы запустить макрос.

Подробные пошаговые инструкции см. в разделе Как вставить код VBA в Excel.

Как запустить макрос из нашего образца книги

Кроме того, вы можете загрузить наш образец рабочей тетради на Дублирование листов Excel и запустить код оттуда.

Образец книги содержит следующие макросы:

Копипшиттоневворкбук – копирует текущий рабочий лист в новую рабочую книгу.

Копировать выбранные листы – копирует несколько выбранных листов в новую книгу.

CopySheetToBeginningAnotherWorkbook – копирует активный лист в начало другой книги.

CopySheetToEndAnotherWorkbook – копирует активный лист в конец другого файла Excel.

Копироватьлист и переименовать – дублирует текущий лист, переименовывает его, как указано пользователем, и помещает копию после всех остальных листов в текущей книге.

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

Копилист и переименовать по ячейке – делает копию активного листа и переименовывает его на основе выбранного значения ячейки.

Копилист и переименовать по ячейке2 – копирует активный лист и переименовывает его на основе жестко заданного адреса ячейки.

Копипшиттоклоседворкбук – позволяет копировать лист в закрытую книгу.

CopySheetFromClosedWorkbook – позволяет скопировать лист из другого файла Excel, не открывая его.

ДубликатлистМультиплетаймс – позволяет дублировать лист в Excel несколько раз.

Чтобы запустить макрос в Excel, просто сделайте следующее:

1. Откройте загруженную книгу и включите содержимое, если будет предложено.
2. Откройте свою книгу и перейдите к листу, который хотите скопировать.
3. На листе нажмите Alt + F8, выберите интересующий макрос и нажмите «Выполнить».

Вот как вы можете дублировать лист в Excel с помощью VBA. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также могут заинтересовать