CODEWITHSUNDEEP
vbaexcel
Azael Contreras
Azael Contreras

Reputation: 37

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

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

Upvotes: 0

Views: 3334

Answers (1)

A.S.H
A.S.H

Reputation: 29332

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

Upvotes: 4

Related Questions