Worksheet run time error 1004 - LoopIndex breaking down

[grexcelman]

Not sure why this is breaking but I have a spreadsheet with dozens of tabs that require updating through this macro loop. It indexes the first and last pages, 30 to 80 but once it reaches the 39th tab, it breaks. I've tried moving the potential bad tab(running it separately at times) but it doesn't change the break. Any thoughts on where this code or my spreadsheet could be getting tripped up?

Worksheets("Monthly Trend").Select

' determine current bounds
    Dim StartIndex, EndIndex, LoopIndex As Integer
    StartIndex = Sheets("Monthly Trend").Index
    EndIndex = Sheets("Transactions").Index
 
    For LoopIndex = StartIndex To EndIndex
    Sheets(LoopIndex).Select
    
       RetrieveSheet
       
    Next LoopIndex

 

[mole999]

unhide all your sheets and give it a go (on a copy)

 

[JoeMo]

There are hidden sheets in the document but I don't know why that would trip it up if I'm referencing by name the first and last sheets. ...would that throw it off regardless?

As I said in my post, you can't select a hidden sheet. Why not get rid of the Select line? Make sure your called procedure "RetrieveSheet" is written so that it does not require the sheet it operates on to be the active sheet.

 

[grexcelman]

As I said in my post, you can't select a hidden sheet. Why not get rid of the Select line? Make sure your called procedure "RetrieveSheet" is written so that it does not require the sheet it operates on to be the active sheet.

Thank you both for your help. I didn't realize that I was also selecting hidden sheets which was the cause of the breakdown. I decided to move those hidden sheets to the end of the document, outside the Index range of my sheets and that did the trick. Thanks again!