Excel макрос копирование листа без формул
Копирует лист в другое расположение в текущей книге или новой книге.
Синтаксис
выражения. Copy (Before, After)
выражение Переменная, представляюная объект "Таблица ".
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Before | Необязательный | Variant | Лист, перед которым будет размещен скопирован лист. Вы не можете указать Перед , если указать После. |
After | Необязательный | Variant | Лист, после которого будет размещен скопирован лист. Вы не можете указать После, если вы указываете Раньше. |
Примечания
Если вы не указываете ни до, ни после, Microsoft Excel создает новую книгу, которая содержит скопированные объекты таблицы. Недавно созданная книга содержит свойство Application.ActiveWorkbook и содержит одну таблицу. В единой таблице сохраняются свойства name и CodeName исходных таблиц. Если скопированная таблица держала лист кода листа в проекте VBA, то это также осуществляется в новой книге.
Выбор массива из нескольких листов можно скопировать на новый пустой объект книги аналогичным образом.
Источник и назначение должны быть в одном Excel. Пример приложения, в противном случае он вызывает ошибку времени запуска '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 и обратная связь.
Sub Сохранение()
On Error Resume Next
' название подпапки, в которую по-умолчанию будет предложено сохранить файл
Const REPORTS_FOLDER = "C:Users\Aman\Desktop\Games"
' ?создаём папку для файла, если её ещё нет
MkDir ThisWorkbook.Path & "\" & REPORTS_FOLDER
' выбираем стартовую папку
ChDrive Left(ThisWorkbook.Path, 1): ChDir "C:\Users\Aman\Desktop\Games"
' вывод диалогового окна для запроса имени сохраняемого файла
Filename = Application.GetSaveAsFilename("PO-13-.xlsx", "Excel (*.xlsx),", , _
"Введите имя файла для сохраняемого отчёта", "Сохранить")
' если пользователь отказался от выбора имени файла - отменяем сохранение листа в файл
If VarType(Filename) = vbBoolean Then Exit Sub
' копируем активный лист (при этом создаётся новая книга)
Err.Clear: ActiveSheet.Copy: DoEvents
If Err Then Exit Sub ' произошла какая-то ошибка при попытке копирования листа
' убеждаемся, что активной книгой является копия листа
If ActiveWorkbook.Worksheets.Count = 1 And ActiveWorkbook.Path = "" Then
ActiveWorkbook.ActiveSheet.DrawingObjects.Delete
ActiveWorkbook.SaveAs Filename, xlOpenXMLWorkbook
' теперь убираем формулы и выпадающие списки с копии листа
For Each c In Selection
c.FormulaR1C1 = c.Value
Next c
' закрываем сохранённый файл
' (удалите следующую строку, если закрывать созданный файл не требуется)
ActiveWorkbook.Close False
End If
End Sub
Sub Сохранение()
On Error Resume Next
' название подпапки, в которую по-умолчанию будет предложено сохранить файл
Const REPORTS_FOLDER = "C:Users\Aman\Desktop\Games"
' ?создаём папку для файла, если её ещё нет
MkDir ThisWorkbook.Path & "\" & REPORTS_FOLDER
' выбираем стартовую папку
ChDrive Left(ThisWorkbook.Path, 1): ChDir "C:\Users\Aman\Desktop\Games"
' вывод диалогового окна для запроса имени сохраняемого файла
Filename = Application.GetSaveAsFilename("PO-13-.xlsx", "Excel (*.xlsx),", , _
"Введите имя файла для сохраняемого отчёта", "Сохранить")
' если пользователь отказался от выбора имени файла - отменяем сохранение листа в файл
If VarType(Filename) = vbBoolean Then Exit Sub
' копируем активный лист (при этом создаётся новая книга)
Err.Clear: ActiveSheet.Copy: DoEvents
If Err Then Exit Sub ' произошла какая-то ошибка при попытке копирования листа
' убеждаемся, что активной книгой является копия листа
If ActiveWorkbook.Worksheets.Count = 1 And ActiveWorkbook.Path = "" Then
ActiveWorkbook.ActiveSheet.DrawingObjects.Delete
ActiveWorkbook.SaveAs Filename, xlOpenXMLWorkbook
' теперь убираем формулы и выпадающие списки с копии листа
For Each c In Selection
c.FormulaR1C1 = c.Value
Next c
' закрываем сохранённый файл
' (удалите следующую строку, если закрывать созданный файл не требуется)
ActiveWorkbook.Close False
End If
End Sub
Sub Сохранение()
On Error Resume Next
' название подпапки, в которую по-умолчанию будет предложено сохранить файл
Const REPORTS_FOLDER = "C:Users\Aman\Desktop\Games"
' ?создаём папку для файла, если её ещё нет
MkDir ThisWorkbook.Path & "\" & REPORTS_FOLDER
' выбираем стартовую папку
ChDrive Left(ThisWorkbook.Path, 1): ChDir "C:\Users\Aman\Desktop\Games"
' вывод диалогового окна для запроса имени сохраняемого файла
Filename = Application.GetSaveAsFilename("PO-13-.xlsx", "Excel (*.xlsx),", , _
"Введите имя файла для сохраняемого отчёта", "Сохранить")
' если пользователь отказался от выбора имени файла - отменяем сохранение листа в файл
If VarType(Filename) = vbBoolean Then Exit Sub
' копируем активный лист (при этом создаётся новая книга)
Err.Clear: ActiveSheet.Copy: DoEvents
If Err Then Exit Sub ' произошла какая-то ошибка при попытке копирования листа
' убеждаемся, что активной книгой является копия листа
If ActiveWorkbook.Worksheets.Count = 1 And ActiveWorkbook.Path = "" Then
ActiveWorkbook.ActiveSheet.DrawingObjects.Delete
ActiveWorkbook.SaveAs Filename, xlOpenXMLWorkbook
' теперь убираем формулы и выпадающие списки с копии листа
For Each c In Selection
c.FormulaR1C1 = c.Value
Next c
' закрываем сохранённый файл
' (удалите следующую строку, если закрывать созданный файл не требуется)
ActiveWorkbook.Close False
End If
End Sub
Здравствуйте! не получается оптимизировать макрос чтобы копировал только значения
Сначала я копирую определенный лист из другой книги
Sub CombineWorkbooks()
Dim filestoopen
Dim x As Integer
Application.ScreenUpdating = False
filestoopen = Application.GetOpenFilename(filefilter:="All files(*.*),*.*", MultiSelect:=True, Title:="Files to Merge")
If TypeName(filestoopen) = "Boolean" Then
MsgBox "Файл не выбран"
Exit Sub
End If
x = 1
While x Set importWB = Workbooks.Open(Filename:=filestoopen(x))
Sheets("Свод").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
importWB.Close savechanges:=False
x = x + 1
Wend
Application.ScreenUpdating = True
Затем я на новом листе собираю свод из текущих листов, все копируется но с формулами
Sub www()
Dim ws As Worksheet, l&
With Sheets("Svod")
.UsedRange.Offset(9).ClearContents
For Each ws In Worksheets
If Not ws.Name = "Svod" Then
l = .Cells.Find("*", [a20], xlValues, 1, 1, 2).Row + 1
ws.UsedRange.Offset(9).Copy .Range("a" & l)
End If
Next
End With
End Sub
Как в свод скопировать только значения?
Спасибо
Здравствуйте! не получается оптимизировать макрос чтобы копировал только значения
Сначала я копирую определенный лист из другой книги
Sub CombineWorkbooks()
Dim filestoopen
Dim x As Integer
Application.ScreenUpdating = False
filestoopen = Application.GetOpenFilename(filefilter:="All files(*.*),*.*", MultiSelect:=True, Title:="Files to Merge")
If TypeName(filestoopen) = "Boolean" Then
MsgBox "Файл не выбран"
Exit Sub
End If
x = 1
While x Set importWB = Workbooks.Open(Filename:=filestoopen(x))
Sheets("Свод").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
importWB.Close savechanges:=False
x = x + 1
Wend
Application.ScreenUpdating = True
Затем я на новом листе собираю свод из текущих листов, все копируется но с формулами
Sub www()
Dim ws As Worksheet, l&
With Sheets("Svod")
.UsedRange.Offset(9).ClearContents
For Each ws In Worksheets
If Not ws.Name = "Svod" Then
l = .Cells.Find("*", [a20], xlValues, 1, 1, 2).Row + 1
ws.UsedRange.Offset(9).Copy .Range("a" & l)
End If
Next
End With
End Sub
Как в свод скопировать только значения?
Спасибо julia5555
Sub CombineWorkbooks()
Dim filestoopen
Dim x As Integer
Application.ScreenUpdating = False
filestoopen = Application.GetOpenFilename(filefilter:="All files(*.*),*.*", MultiSelect:=True, Title:="Files to Merge")
If TypeName(filestoopen) = "Boolean" Then
MsgBox "Файл не выбран"
Exit Sub
End If
x = 1
While x Set importWB = Workbooks.Open(Filename:=filestoopen(x))
Sheets("Свод").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
importWB.Close savechanges:=False
x = x + 1
Wend
Application.ScreenUpdating = True
Затем я на новом листе собираю свод из текущих листов, все копируется но с формулами
Sub www()
Dim ws As Worksheet, l&
With Sheets("Svod")
.UsedRange.Offset(9).ClearContents
For Each ws In Worksheets
If Not ws.Name = "Svod" Then
l = .Cells.Find("*", [a20], xlValues, 1, 1, 2).Row + 1
ws.UsedRange.Offset(9).Copy .Range("a" & l)
End If
Next
End With
End Sub
Как в свод скопировать только значения?
Спасибо Автор - julia5555
Дата добавления - 21.03.2018 в 09:15
Про сборку листов из нескольких книг в одну текущую я уже писал здесь. Теперь разберем решение обратной задачи: есть одна книга Excel, которую нужно "разобрать", т.е. сохранить каждый лист как отдельный файл для дальнейшего использования.
Примеров подобного из реальной жизни можно привести массу. Например, файл-отчет с листами-филиалами нужно разделить на отдельные книги по листам, чтобы передать затем данные в каждый филиал и т.д.
Если делать эту процедуру вручную, то придется для каждого листа выполнить немаленькую цепочку действий (выбрать лист, правой кнопкой по ярлычку листа, выбрать Копировать, указать отдельный предварительно созданный пустой файл и т.д.) Гораздо проще использовать короткий макрос, автоматизирующий эти действия.
Способ 1. Простое разделение
Нажмите сочетание Alt+F11 или выберите в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Если теперь выйти из редактора Visual Basic и вернуться в Excel, а затем запустить наш макрос (Alt+F8), то все листы из текущей книги будут разбиты по отдельным новым созданным книгам.
Способ 2. Разделение с сохранением
При необходимости, можно созданные книги сразу же сохранять под именами листов. Для этого макрос придется немного изменить, добавив команду сохранения в цикл:
Этот макрос сохраняет новые книги-листы в ту же папку, где лежал исходный файл. При необходимости сохранения в другое место, замените wb.Path на свой путь в кавычках, например "D:\Отчеты\2012" и т.п.
Если нужно сохранять файлы не в стандартном формате книги Excel (xlsx), а в других (xls, xlsm, xlsb, txt и т.д.), то кроме очевидного изменения расширения на нужное, потребуется добавить еще и уточнение формата файла - параметр FileFormat:
Для основных типов файлов значения параметра FileFormat следующие:
- XLSX = 51
- XLSM = 52
- XLSB = 50
- XLS = 56
- TXT = 42
Способ 3. Сохранение в новые книги только выделенных листов
Если вы хотите раскидать по файлам не все листы в вашей книге, а только некоторые, то макрос придется немного изменить. Выделите нужные вам листы в книге, удерживая на клавиатуре клавишу Ctrl или Shift и запустите приведенный ниже макрос:
Создавать новое окно и копировать через него, а не напрямую, приходится потому, что Excel не умеет копировать группу листов, если среди них есть листы с умными таблицами. Копирование через новое окно позволяет такую проблему обойти.
Способ 4. Сохранение только выделенных листов в новый файл
Во всех описанных выше способах каждый лист сохранялся в свой отдельный файл. Если же вы хотите сохранить в отдельный новый файл сразу группу выделенных предварительно листов, то нам потребуется слегка видоизменить наш макрос:
Способ 5. Сохранение листов как отдельных PDF-файлов
- для этого используется уже другой метод (ExportAsFixedFormat а не Copy)
- листы выводятся в PDF с параметрами печати, настроенными на вкладке Разметка страницы (Page Layout)
- книга должна быть сохранена на момент экспорта
Нужный нам код будет выглядеть следующим образом:
Способ 6. Готовый макрос из надстройки PLEX
Если лень или нет времени внедрять все вышеописанное, то можно воспользоваться готовым макросом из моей надстройки PLEX:
Про сборку листов из нескольких книг в одну текущую я уже писал здесь. Теперь разберем решение обратной задачи: есть одна книга Excel, которую нужно "разобрать", т.е. сохранить каждый лист как отдельный файл для дальнейшего использования.
Примеров подобного из реальной жизни можно привести массу. Например, файл-отчет с листами-филиалами нужно разделить на отдельные книги по листам, чтобы передать затем данные в каждый филиал и т.д.
Если делать эту процедуру вручную, то придется для каждого листа выполнить немаленькую цепочку действий (выбрать лист, правой кнопкой по ярлычку листа, выбрать Копировать, указать отдельный предварительно созданный пустой файл и т.д.) Гораздо проще использовать короткий макрос, автоматизирующий эти действия.
Способ 1. Простое разделение
Нажмите сочетание Alt+F11 или выберите в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Если теперь выйти из редактора Visual Basic и вернуться в Excel, а затем запустить наш макрос (Alt+F8), то все листы из текущей книги будут разбиты по отдельным новым созданным книгам.
Способ 2. Разделение с сохранением
При необходимости, можно созданные книги сразу же сохранять под именами листов. Для этого макрос придется немного изменить, добавив команду сохранения в цикл:
Этот макрос сохраняет новые книги-листы в ту же папку, где лежал исходный файл. При необходимости сохранения в другое место, замените wb.Path на свой путь в кавычках, например "D:\Отчеты\2012" и т.п.
Если нужно сохранять файлы не в стандартном формате книги Excel (xlsx), а в других (xls, xlsm, xlsb, txt и т.д.), то кроме очевидного изменения расширения на нужное, потребуется добавить еще и уточнение формата файла - параметр FileFormat:
Для основных типов файлов значения параметра FileFormat следующие:
- XLSX = 51
- XLSM = 52
- XLSB = 50
- XLS = 56
- TXT = 42
Способ 3. Сохранение в новые книги только выделенных листов
Если вы хотите раскидать по файлам не все листы в вашей книге, а только некоторые, то макрос придется немного изменить. Выделите нужные вам листы в книге, удерживая на клавиатуре клавишу Ctrl или Shift и запустите приведенный ниже макрос:
Создавать новое окно и копировать через него, а не напрямую, приходится потому, что Excel не умеет копировать группу листов, если среди них есть листы с умными таблицами. Копирование через новое окно позволяет такую проблему обойти.
Способ 4. Сохранение только выделенных листов в новый файл
Во всех описанных выше способах каждый лист сохранялся в свой отдельный файл. Если же вы хотите сохранить в отдельный новый файл сразу группу выделенных предварительно листов, то нам потребуется слегка видоизменить наш макрос:
Способ 5. Сохранение листов как отдельных PDF-файлов
- для этого используется уже другой метод (ExportAsFixedFormat а не Copy)
- листы выводятся в PDF с параметрами печати, настроенными на вкладке Разметка страницы (Page Layout)
- книга должна быть сохранена на момент экспорта
Нужный нам код будет выглядеть следующим образом:
Способ 6. Готовый макрос из надстройки PLEX
Если лень или нет времени внедрять все вышеописанное, то можно воспользоваться готовым макросом из моей надстройки PLEX:
Читайте также: