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

Использование функции 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).

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

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

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

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

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

Допустим необходимо приготовить планы работ для сотрудников вашего отдела. Иметься шаблон таблицы для заполнения документа плана в виде одного рабочего листа 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