为什么Excel VBA在某些工作表上生成错误"Copy method of Sheets class failed"，而在其他工作表上没有？

Question

我正在尝试提出代码，这些代码将在给定的工作簿中副本副本。似乎很简单，对吧？一些Google搜索，我将以下代码拼凑在一起：

Sub Commandbutton1_click()
Dim Cnt    As Long
Dim i      As Long
Dim Sht1   As String
Dim MyChoice As String
Dim MyFile As String
Dim CurrWorkBook As Excel.Workbook
Dim Month As String
'Instructional message box
MsgBox "When the 'Open' dialog appears, select the workbook containing the worksheets you want to split and then click Ok."
'Get file name
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Show
    MyChoice = .SelectedItems(1)
End With
Application.ScreenUpdating = False
MyFile = Dir(MyChoice)
Set CurrWorkBook = Workbooks.Open(Filename:=MyFile)
CurrWorkBook.Activate
Cnt = Sheets.Count
InputMsg = "Enter the month of the EOM Budget Review:"
InputTitle = "Month"
Month = InputBox(InputMsg, InputTitle)
For i = 1 To Cnt Step 1
    Sht1 = Sheets(i).Name
    Sheets(Array(Sht1)).Copy
        ActiveWorkbook.SaveAs Filename:=Sht1 & " - " & Month & " EOM Budget Review.xlsx", _
                              FileFormat:=51, Password:="", WriteResPassword:="", _
                              ReadOnlyRecommended:=False, CreateBackup:=False
        ActiveWorkbook.Close
Next i
CurrWorkBook.Save
CurrWorkBook.Close
Application.ScreenUpdating = True
End Sub

它可以完美地工作...除非没有。在某些工作簿中，它将毫无困难地复制每张纸。在某些工作簿中，它将复制一些床单，但是将"床单的复制方法失败"，除非您跳过某些床单。我无法弄清楚它不会复制的床单。有什么办法可以改进此代码吗？是否有某些工作表的功能会导致这种代码不可避免地失败？

Answer 1

借助上面的Alex P.的评论解决了。我从另一个论坛复制了以下代码：

Sub UnhideAll()
Dim WS As Worksheet
    For Each WS In Worksheets
        WS.Visible = True
    Next
End Sub

然后，我在Application.ScreenUpdating = False之后立即使用了Call UnhideAll。最后，我还使用了CurrWorkBook.Close savechanges:=False，因此复制的工作簿不会保存，其隐藏的工作表将返回被隐藏。