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]

is it the same tab that breaks, what number is the Tab, whats its name

 

[grexcelman]

is it the same tab that breaks, what number is the Tab, whats its name

The tab number remains 39 and that doesn't change even when I swap the order around. I thought maybe it was the tab name, given it had spaces in it? That didn't seem to make a difference.

 

[mole999]

is there any protection or strange cell merges

 

[grexcelman]

No protection but there are plenty of data groupings, not sure how you mean by "strange" cell merges but there are no unique cell mergers on anyone particular page. All the pages have the same formatting properties and that includes cell mergers. Again, this runs about 10 sheets before it breaks. When I close out the module and return to the spreadsheet, I can run it separately without error. Note, this is running an essbase retrieve function on each sheet but I can't seem to duplicate the error when I run it individually.

 

[mole999]

Maybe a DoEvents at the end of each loop ?

 

[grexcelman]

Maybe a DoEvents at the end of each loop ?

Are you suggesting to add this? - how would you add that syntax?

 

[mole999]

DoEvents
Next LoopIndex

 

[JoeMo]

You can't select a hidden sheet. Is the 39th sheet hidden?

 

[grexcelman]

You can't select a hidden sheet. Is the 39th sheet hidden?

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?