Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Code is bombing out.

Status
Not open for further replies.

jrobin5881

Technical User
Mar 10, 2004
228
US
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
 
Do you have any hidden sheets? Can you check?

Member- AAAA Association Against Acronym Abusers
 



you might be loosing reference???
Code:
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,

[glasses] [red][/red]
[tongue]
 
My "Personal" workbook was hidden. Would that cause it? I have the macro stored in a different workbook, not the Personal book.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top