Why does Excel VBA generate the error “Copy method of Sheets class failed” on some sheets, but not others?

Question

I am trying to come up with code that will make copies of all the worksheets in a given workbook. Seems simple enough, right? A little Google searching and I cobbled together the following code:

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

It works perfectly...except when it doesn't. In some workbooks, it will copy every sheet with no difficulty. In some workbooks, it will copy some of the sheets, but throw the "Copy method of Sheets class failed" unless you have it skip certain sheets. I have not been able to figure out what the sheets it will not copy have in common. Is there some way I can improve this code? Are there certain features of worksheets that will cause this kind of code to fail inevitably?

One reason would be if the visibility of the sheet you are trying to copy is set as xlSheetHidden or xlSheetVeryHidden — Alex P
Good call! The workbooks it was having trouble with contained hidden worksheets I did not know about. I wrote a separate sub to unhide all worksheets and had it called right before the copy loop. Now it has no problems. — SunnyvaleSupervisor

SunnyvaleSupervisor SunnyvaleSupervisor · Accepted Answer · 2015-05-08T12:57:36

Solved thanks to Alex P.'s comment above. I copied the following code from another forum:

Sub UnhideAll()

Dim WS As Worksheet

    For Each WS In Worksheets
        WS.Visible = True
    Next

End Sub

Then I used Call UnhideAll right after Application.ScreenUpdating = False. I also used CurrWorkBook.Close savechanges:=False at the end so that the workbook being copied would not be saved and its hidden worksheets would go back to being hidden.

Why does Excel VBA generate the error “Copy method of Sheets class failed” on some sheets, but not others?

1 Answers