Excel копировать весь лист vba

Метод Sheets.Copy (Excel)

Копирует лист в другое место в книге.

Синтаксис

expression. Копирование (до, после)

выражение: переменная, представляющая объект Sheets.

Параметры

Замечания

Если не указать значение «До» или «После», Microsoft Excel создает новую книгу, содержащую скопированный лист.

Пример

В этом примере выполняется копирование Sheet1, помещая его после Sheet3.

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

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

Источник

Метод Worksheet.Copy (Excel)

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

Синтаксис

expression. Копирование (до, после)

Выражение Переменная, представляющая объект Worksheet .

Параметры

Замечания

Если не указать значение «До» или «После», Microsoft Excel создает новую книгу, содержащую скопированный объект Worksheet . Только что созданная книга содержит свойство Application.ActiveWorkbook и содержит один лист. На одном листе сохраняются свойства Name и CodeName исходного листа. Если скопированный лист содержал лист кода листа в проекте VBA, он также переносится в новую книгу.

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

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

Возможности макросов в 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 будет присваивать сам. Например, для исходного листа с названием «Лист1» копии будут получать названия: «Лист1 (2)», «Лист1 (3)», «Лист1 (4)» и т.д.

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

Источник

Sheets.Copy method (Excel)

Copies the sheet to another location in the workbook.

Syntax

expression.Copy (Before, After)

expression A variable that represents a Sheets object.

Parameters

If you don’t specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.

Example

This example copies Sheet1, placing the copy after Sheet3.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник

How to duplicate sheet in Excel with VBA

by Svetlana Cheusheva, updated on March 16, 2023

The tutorial provides a collection of macros to duplicate sheets in Excel: copy and rename based on cell value, copy multiple sheets, copy an active worksheet to another file without opening it, and more.

Manually copying sheets in Excel is pretty quick and straightforward. if performed just once or twice. Duplicating multiple sheets multiple times is boring and time consuming. On this page, you will find a handful of useful macros to automate this task.

Excel VBA to copy sheet to new workbook

This simplest one-line macro does exactly what its name suggests — copies the active sheet to a new workbook.

Copy multiple sheets in Excel with VBA

If you’d like to copy several sheets from the active workbook to a new one, select all the worksheets of interest and run this macro:

Excel VBA to copy sheet to another workbook

Depending on where you want to insert the copied sheet, use one of the following macros.

Copy sheet to the beginning of another workbook

This macro copies the active sheet before all other worksheets in the destination file, Book1 in this example. To copy to another file, replace «Book1.xlsx» with the full name of your target workbook.

Copy sheet to the end of another workbook

This piece of code duplicates the active worksheet and places the copy to the end of Book1. Again, please remember to replace «Book1.xlsx» with the name of your destination workbook.

Note. For the macros to work, the target workbook must be saved on your hard drive or network.

Copy sheet to a selected workbook

To be able to copy the current sheet to any open workbook, you can create a UserForm (named UserForm1) with a ListBox control (named ListBox1) and two buttons:

Next, double-click the form and paste the below code in the Code window:

With the UserForm in place, you can use one of the following macros to copy the active sheet to the workbook of your choosing.

Copy sheet to the beginning of the selected workbook:

Copy sheet to the end of the selected workbook:

When run in Excel, the macro will show you a list of all currently opened workbooks. You select the needed one and click OK:

Excel macro to copy sheet and rename

When you copy a sheet in Excel, the replica is given a name in the default format like Sheet1 (2). The following macros can spare you the trouble of changing the default name manually.

This code duplicates the active worksheet, names the copy as «Test Sheet» (you are free to replace it with any other name you like), and places the copied sheet at the end of the current workbook.

To allow the user to specify the name for the copied sheet, use this code:

Upon running, the macro displays the following input box, in which you type the desired name and press OK:

Excel macro to copy sheet and rename based on cell value

In some situations, it may be more convenient to name a copy with a specific cell value, for example, a column header. For this, you simply take the above code and supply the value of the currently selected cell to the input box automatically. As with the previous example, the copy will be placed at the end of the active workbook.

The trickiest part would be to have your users always select the correct cell before running the macro 🙂

Alternatively, you can hardcode the address of the cell by which the copy should be named, cell A1 in the below code. To name the copied worksheet based on another cell, replace A1 with an appropriate cell reference.

Macro to copy worksheet to a closed workbook

This macro copies the active sheet to the end of a closed workbook. The name of another workbook is not specified in the code — the macro will open the standard Windows Explorer window and allow you to choose any destination file:

After you select the file and click Open, the macro will copy the active sheet and close the target workbook automatically.

Excel VBA to copy sheet from another workbook without opening

This macro enables you to copy a worksheet from another Excel file without opening it. The copied sheet will be inserted at the end of the current workbook.

Just remember to make a couple of replacements in the code:

• C:UsersXXXDocumentsTarget_Book.xlsx should be changed to the actual path and name of the workbook from which you want to copy a sheet.
• Sheet1 should be replaced with the name of the sheet you want to copy.

Excel VBA to duplicate sheet multiple times

Sometimes, you may need to duplicate the same sheet more than once, for instance to test different formulas on the same data set. This can be easily done with the following macro.

Open the original sheet, run the macro, specify how many copies of the active sheet you want to make, and click OK:

How to duplicate sheets in Excel with VBA

To copy a sheet in Excel with one of the above macros, you can either insert the VBA code into your own book or run a macro from our sample workbook.

How to add a macro to your workbook

To insert the code in your workbook, perform these steps:

1. Open the worksheet you want to copy.
2. Press Alt + F11 to open the Visual Basic Editor.
3. On the left pane, right-click ThisWorkbook, and then click Insert >Module.
4. Paste the code in the Code window.
5. Press F5 to run the macro.

For the detailed step-by-step instructions, please see How to insert VBA code in Excel.

How to run a macro from our sample workbook

Alternatively, you can download our sample workbook to Duplicate Excel Sheets and run the code from there.

The sample workbook contains the following macros:

CopySheetToNewWorkbook — copies the current worksheet to a new workbook.

CopySelectedSheets — copies multiple sheets that you select to a new workbook.

CopySheetToBeginningAnotherWorkbook — copies the active sheet to the beginning of another workbook.

CopySheetToEndAnotherWorkbook — copies the active sheet to the end of another Excel file.

CopySheetAndRename — duplicates the current sheet, renames it as specified by the user, and puts the copy after all other sheets in the current workbook.

CopySheetAndRenamePredefined — duplicates the active sheet, gives a hardcoded name to the copy and places it at the end of the current workbook.

CopySheetAndRenameByCell — makes a copy of the active sheet and renames it based on the selected cell value.

CopySheetAndRenameByCell2 — copies the active sheet and renames it based on the hardcoded cell address.

CopySheetToClosedWorkbook — allows you to copy sheet to a closed workbook.

CopySheetFromClosedWorkbook — enables you to copy a sheet from another Excel file without opening it.

DuplicateSheetMultipleTimes — lets you duplicate a sheet in Excel multiple times.

To run the macro in your Excel, just do the following:

1. Open the downloaded workbook and enable the content if prompted.
2. Open your own workbook and navigate to the sheet you want to copy.
3. In your worksheet, press Alt + F8 , select the macro of interest, and click Run.

That’s how you can duplicate a sheet in Excel with VBA. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

Table of contents

I am running the «Excel macro to copy sheet and rename» where it allows the user to save a new name. However, when I create it, it renames the old version then creates a duplicate of the original and puts in for example test (2). I want to keep the original and have the new named sheet at the end of the workbook. Here is my coding:
Sub CopySheetAndRename()
Dim newName As String

On Error Resume Next
newName = InputBox(«Enter the name for the copied worksheet»)

If newName «» Then
Sheets(«Portfolio Plus»).Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
Sheets(«Portfolio Plus»).Name = newName
End If
End Sub

I have a worksheet where I would like to copy two worksheets and rename them. The two worksheets are named by date. How could I amend the macro to name the two new worksheets with the date+1 of the previous worksheets?

I have a template with named ranges in tables that I use to export data from SAS (One range per sheet, 9 sheets). I keep ending up with corrupted files and I was told to copy everything to a new workbook and try again. However, I get an error «You cannot copy or move a group of sheets that contain tables.»
I am very new to VBA, so any advice is appreciated!

Hi!
Convert your tables to a normal Excel range. VBA macros do not work with tables.
The following tutorial should help: Convert Excel table to range and turn data range into table.

I have set up the COPY A WORKSHEET AND RENAME macro but it copies the new worksheet AFTER (to the right of) the source. I want it to copy before..I tried changing the «After» command to «Before» but this did not work. suggestions?

Quiero copiar la hoja T3 y reemplazar con ella las hojas T1 y T2 sin que se disparen errores de Ref! en la hoja Indice

HI
Is there a way to run a macro to copy all comment thread/notes from cells to a new worksheet?
I’ve tried and found VB code but it didnt seem to work, I’m using office 365.
Thanks

Hello.
I am trying to place a shape button on sheets in a workbook so when data is filled for a week. I like copy this sheet say name of workbook is » working on» and sheet name » weekly report» when this page is filed for the week. I want copy this sheet in to closed work book say name is «weekly reports Archive» and place it at end of all sheets and name the sheet i am copying to «weekly report wk 21» after weekly report 20 sheet. All work books and sheets are shared for updates and want to make it more easier for others to update. Would you be able to help. I am new to VBA coding.

I recived «path not found ‘.vbbad70.tmp’ while i used:
Sheets(SheetCount).Copy After:=Sheets(SheetCount)

Hi thank you very much for these codes, it really saves a lot of time.I have question in regards to the copying of a sheet to a closed workbook.When I tried to do that it gives me a runtime error’91’ «currentSheet.Copy After:=closedBook.Sheets(closedBook.Worksheets.Count)».I’m copying to a hard drive. Will be glad if you can assist. Regards, Jan.

Hi,
It is very useful.
Can you help me with the codes to copy multiple sheets (Only values & format) from workbook to new workbook.
Thanks!

Amazing thank you! Did exactly what I needed it to (with some easy modifications)

Hi,
re Power Pivot / Manage in Data Model
I get this message «PowerPivot is unable to load the Data Model.»
I have tried a lot of ways to sort this problem, but no luck.
Do you think it would work if I export all the modules and Sheets individually and then import them into a new clear Workbook (.xlsm)

Dear Sir,
I have a workbook in which a sheet with some formulas. I want a duplicate copy of this sheet and rename with a cell value. And I want this duplicate sheet with Cell values only (as paste special). I did it with the help of your formulas but it is copy and pasting on the source sheet not on duplicate sheet. Please help me.
Private Sub CommandButton1_Click()
Dim wks As Worksheet
Set wks = ActiveSheet
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wks.Range(«b9»).Value «» Then
On Error Resume Next
ActiveSheet.Name = wks.Range(«b9»).Value
End If
CommandButton2_Click
‘wks.Activate
End Sub
Private Sub CommandButton2_Click()
Worksheets(Sheets.Count).Activate
Range(«a1:s36»).Copy
Range(«a1:s36»).PasteSpecial xlPasteValues
End Sub

Morning all,
The above macros are extremely useful, but is there a way to copy the sheet across as «values only», similar to the PasteSpecial method?

Could anyone please help me on how to copy sheets with shapes?
My code is referencing to a specific shape, therefore when I copy the sheet, names of the shapes change.
How can I prevent this?
Is there a way to write a code that doesn’t have to reference each shape?
Thank you for your help.

I am trying to duplicate a excel sheet multiple times, rename them based on an Excel List, and then populate the column next to the sheet name with a link to the sheet.
The list is found in the «Cover» sheet and I want to copy the «Template» sheet based on the list selection. The link shortcut will be in the cover sheet in the column next to the cell that was used to generate the sheet name. There will be a lot of tabs and this will make life a lot easier if there is a link from the cover page
Is this possible, and if so how can I do it?

Hi
Is it possible to overwrite a sheet of the same name in the worksheet where the sheet is to be copied? I need to be able to copy the same sheet from time to time. Please help?

Hi,
Your macros have been extremely helpful.
I really appreciate your work. Thank you.
Thank you so much.

Excel macro to copy sheet and rename

Is there anyway for the button to be on one sheet but instead of copying the active sheet it copies the sheet called New Hire?

Excel VBA to duplicate sheet multiple times:
This is the closest to what I am trying to do. But all the new sheets are named he same as the original but with (x). i.e. Original sheet is 10001 and I need them to number 10002, 10003, etc. But they are numbering 10001, 10001(1), 10001(2).
How can I get my sheet tabs to number the way I want? Any help would be greatly appreciated.
Thanks!

Thanks for this vba «Excel VBA to copy sheet from another workbook without opening» which works excellently in its current form. Can you please tweak it a little so that instead of giving a specific path, a window opens and I select the file from which data needs to be pulled. Rest it should remain the same. Thanks in advance.

Hi I have used your code titled «Excel VBA to duplicate sheet multiple times»

but how shall I rename the sheets? I want them to be in numbers.

Eg
Original Sheet is «master’
Copy need 5
result shall be in sheet name as
Point 1
Point 2
Point 3
Point 4
Point 5

—[Snip, Snip]—
Public Sub DuplicateSheetMultipleTimes()
Dim n As Integer
On Error Resume Next
n = InputBox(«How many copies of the active sheet do you want to make?»)

If n >= 1 Then
For numtimes = 1 To n
activeSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
ActiveSheet.name = «Point » + CStr(numtimes)
Next
End If
End Sub
—[Snip, Sni

This will cause the various copied sheets to be renamed as they are copied

Hi,
I have a workbook with many sheets. I need particular cells copied from sheet 1,sheet2 of Workbook1 and show it in another workbook. For example I have the dealer ID and name in Sheet1 and the rating in sheet5 of the same workbook1. I need to pull the rating using ID which is unique to another worksheet and then I need the ID field to be dynamic. How do I do it? please advise.

Is it possible to pick up a range of tabs? For example, the macro requires that I list the tab names individually ie.Sheets(Array («Tab1», «Tab2», «Tab3», “etc…”)).Copy)
What if I want to pick up all the tabs between «Tab1» and «Tab8» without listing them all individually? Is this possible?

Hello, your macros have been extremely helpful.
I was wondering if the following can be achieved
1) I select a range, say A2-A15, which contains the names of worksheets in a particular workbook.
2) I want to create a new workbook with the selected worksheet names from the active workbook using the following formula:
“
Sub Copy_Worksheets()
Worksheets(Array(«ACC_HF», «ACC_Aflac», “etc…”)).Copy
End Sub
”
Is there a way to convert the selected range and pass is to the array argument?

How can i copy data from a range of cells say B5:I39 from multiple worksheets and paste them to one new worksheet, i want to be able to have all selected worksheets with specific data range to be copied to one worksheet at the end of the workbook. All formatting from selected sheets to be coppied across too.

Thanks for the tip, is there a way to copy a row in one workbook and paste that row of data to a different workbook on a specific cell? There is a catch though, the data will need to sync to a different tab of the same workbook every other month.

That’s great
how can I copy sheets containing a specific text string somewhere on the sheet, to a new workbook?

Источник