Code is bombing out.

[jrobin5881]

I've been wrestling with this code for awhile today and I can't figure out why the loop runs once and then bombs out. Here's what I'm trying to do, select two sheets in the current workbook, copy and paste into a new workbook and save it based on the name in the Sheets(i)tab. It runs one iteration fine and then on the next one it bombs out on this line: Sheets(Array(i, j)).Select
The debug message reads:"Runtime error 1004. Select method of sheets class failed" Code is below

Sub looper()

j = 11

For i = 2 To 7
Sheets(Array(i, j)).Select
Sheets(i).Activate
strName = ActiveSheet.Name
Sheets(Array(i, j)).Copy
ActiveWorkbook.SaveAs "T:\OPS\OPSDEL\DELIVERY\Oppty\Final_Report\" & strName & "WK_27"
DisplayAlerts = False
ActiveWorkbook.Close
DisplayAlerts = True
Windows("OpportunityTelecomWeek_FinalReport_Master").Activate
j = j + 1
Next

End Sub

 

[xlhelp]

Do you have any hidden sheets? Can you check?

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

you might be loosing reference???

Sub looper()
    Dim wsThis As Workbook
    
    j = 11
    Set wsThis = ThisWorkbook
    
    For i = 2 To 7
'        Sheets(Array(i, j)).Select
        With wsThis.Sheets(i)
            strName = .Name
            .Copy
            ActiveWorkbook.SaveAs "T:\OPS\OPSDEL\DELIVERY\Oppty\Final_Report\" & strName & "WK_27"
            DisplayAlerts = False
            ActiveWorkbook.Close
            DisplayAlerts = True
        End With
        j = j + 1
    Next
 
End Sub

Skip,

_[red][/red]

 

[SkipVought]

...which is essentially what xlhelp is referring to.

Skip,

_[red][/red]

 

[jrobin5881]

My "Personal" workbook was hidden. Would that cause it? I have the macro stored in a different workbook, not the Personal book.

 

[kjv1611]

jrobin5881,

It wouldn't be hidden WORKBOOKS that would be the problem, but rather WORKSHEETS as they are within your active workbook.

--

"If to err is human, then I must be some kind of human!" -Me