VBA:复制工作簿，删除谢特，然后使用其他名称保存

Question

我正在尝试执行以下操作：

• 复制工作簿
• 移除一些工作表
• 将其另存为其他文件名

这是我的尝试，但它不起作用：

Private Sub publish()
    Dim new_wb As Workbook
    'Doesnt seem to compile??
    Set new_wb = ActiveWorkbook.Sheets.Copy
    For i = new_wb.Sheets.Count To 1
        If InStr(LCase(new_wb.Sheets(i).CodeName), "output") = 0 Then
            new_wb.Sheets(i).Delete
        End If
        i = i - 1
    Next
    Application.DisplayAlerts = False
    new_wb.SaveCopyAs Filename:=Range("output_path").Value
    new_wb.Close
    Application.DisplayAlerts = True
End Sub

有人可以帮我哪里出错吗？

Answer 1

根据以下代码中的注释进行如下改进：

Private Sub publish()
    Dim new_wb As Workbook
    'let's separate your solution into two lines
    ActiveWorkbook.Sheets.Copy
    Set new_wb = ActiveWorkbook
    Dim i as integer
    'add this to make sheets deletion silently
    Application.DisplayAlerts = false
    'add step -1 at the end of this
    For i = new_wb.Sheets.Count To 1 Step -1
        If InStr(LCase(new_wb.Sheets(i).CodeName), "Arkusz1") = 0 Then
            new_wb.Sheets(i).Delete
        End If
        'this is not required any more:
        'i = i - 1
    Next
    'set back alerts to true which is a good habit
    Application.DisplayAlerts = true
    'rest unchanged
    Application.DisplayAlerts = False
    'here you could have an error if 'output_path' doesn't exist in new workbook
    new_wb.SaveCopyAs Filename:=Range("output_path").Value
    new_wb.Close
    Application.DisplayAlerts = True
End Sub