Run time Error 1004: Method 'Select' of Object 'Sheets' failed

Question

I don't know why this is making a run time error. It is supposed to select the sheets in workbook 1 to copy them in workbook 2. Can anyone help me? Debugging shows that the error is in the line: "ActiveWorkbook.Sheets.Select"

Private Sub CommandButton1_Click()
'On Error GoTo ErrorHandler
If cef.path = "" Then
    MsgBox ("PATH of pictures is required")
Else
    Dim WB1 As Workbook
    Dim WB2 As Workbook

    Dim num As Double

    Set WB1 = ActiveWorkbook
    ActiveWorkbook.Sheets("Item_number").Visible = True
    ActiveWorkbook.Sheets("CODES").Visible = True
    ActiveWorkbook.Sheets("Item_Number_Hydro").Visible = True
    ActiveWorkbook.Sheets.Select
    ActiveWindow.SelectedSheets.Copy
    Set WB2 = ActiveWorkbook
    Dim nombreHoja As String

score 4 · Accepted Answer · edited Jun 06 '17 at 14:08

You must have some hidden sheets in the workbook. You cannot select hidden worksheets, and so you cannot Activeworkbook.Sheets.Select which attempts to select all the sheets.

You don't actually need to use Select. But you can copy all the sheets without selecting them, including hidden ones:

ActiveWorkbook.Sheets.copy

Or, copy an array of specific sheets (without Select):

ActiveWorkbook.Sheets(Array("Item_number","CODES","Item_Number_Hydro")).Copy

Run time Error 1004: Method 'Select' of Object 'Sheets' failed

1 Answers1