excel - Excel VBA 从现有代码中保存多个工作表

Question

在发布之前我无法找到具体的答案。

有人可以协助调整我现有的 Excel 中的 VBA 代码，使我能够复制 2 张，而不仅仅是 1 张？我收到一个错误。它适用于 1 张纸，但不适用于 2 张纸。我已将失败的代码以粗体显示。

Sub Export()

Dim FlSv As Variant
Dim MyFile As String
Dim MyTemplate As String
Dim sh As Worksheet
Dim wbNew As Workbook

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

这是失败的地方：

Set sh = ActiveWorkbook.Sheets("Sheet 1", "Sheet 2")
    sh.Copy

    Set wbNew = ActiveWorkbook

    MyFile = Replace("Consolidated", ".xlsm", "")

    FlSv = Application.GetSaveAsFilename(MyFile, fileFilter:="Excel Files (*.xlsx), *.xlsx)", Title:="Enter your file name")

    wbNew.SaveAs FlSv, FileFormat:=51
    wbNew.Close

    For Each s In ActiveWorkbook.Sheets
        If s.Name Like "*Sheet 1*" Then
            Application.DisplayAlerts = False
            s.Delete
            Application.DisplayAlerts = True
        End If
    Next s

  For Each s In ActiveWorkbook.Sheets
        If s.Name Like "*Sheet 2*" Then
            Application.DisplayAlerts = False
            s.Delete
            Application.DisplayAlerts = True
        End If
    Next s

    End Sub

也试过这个：

Sub Export()

Dim FlSv As Variant
Dim MyFile As String
Dim MyTemplate As String
Dim sh As Worksheet
Dim wbNew As Workbook

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'Set sh = ActiveWorkbook.Sheets("Overrun")
'sh.Copy
'Set wbNew = ActiveWorkbook

ActiveWorkbook.workheets(Array("Sheet 1", "Sheet 2")).Copy
'there is a new activeworkbook with two worksheets
Set wbNew = ActiveWorkbook

MyFile = Replace("Consolidated", ".xlsm", "")

FlSv = Application.GetSaveAsFilename(MyFile, fileFilter:="Excel Files (*.xlsx), *.xlsx)", Title:="Enter your file name")

wbNew.SaveAs FlSv, FileFormat:=51
wbNew.Close

For Each s In ActiveWorkbook.Sheets
    If s.Name Like "*Overrun*" Then
        Application.DisplayAlerts = False
        s.Delete
        Application.DisplayAlerts = True
    End If
Next s

End Sub
Sub all()

End Sub

标签： excelvbasaveworksheet

Answer 1

使用工作表名称数组。

dim wb as workbook

ActiveWorkbook.sheets(array("Sheet 1", "Sheet 2")).copy
'there is a new activeworkbook with two worksheets
set wb = ActiveWorkbook