Subscript Out Of Range Error

I have a large workbook with several macros that refreshes all of the data for a specific year. I have some code on my Dashboard that calls the main Refresh macro when a specific cell is changed on my Dashboard sheet. If I just click the run button in the VBA editor for the main Refresh macro, the code runs perfectly with no problem every time. However, if I run the macro from the code on the Dashboard sheet, it (sometimes) errors out with a Subscript out of range error. Sometimes it runs fine all the way through, and sometimes it errors out, seemingly randomly.

It always errors out on this line:

Please Login or Register  to view this content.

Here is my code on the Dashboard sheet:

Please Login or Register  to view this content.

And here is my main code:

Please Login or Register  to view this content.

Thanks for the reply!

The values of those two variables vary (haha) between refreshes.

When I hover over BookName, or NextSheet in break mode they will give me the proper values.

The sheet does exist - it will say like Sheet59, or Sheet138 when I hover over VBComponents(Sheets(BookName).CodeName).

And I really can't think of one thing I do differently to prepare the sheet when I run directly from the main Refresh macro.

The purpose of the code is indeed to change the Code Name (the name of the sheet it shows in the VBA Editor). It does this because every time it refreshes it is deleting and adding up to 20 new worksheets. It just failed for the first time today. I swear I ran it through from the Dashboard code at least 25 or more times and it worked flawlessly.

However, it just failed. Im attaching a screen shot with some watches showing both that BookName does exist (blue), and NextSheet (green) does not exist.

Screen Shot.jpg

I want to do this for several reasons:

1. Even after deleting the sheets the VBA editor will continue to number the sheets sequentially so if I refresh this several times, its quickly enters the 100's and it becomes cluttered and difficult to navigate inside the VBA editor.
2. Since the sheet names (not the code names) change each time without any predictability, it is difficult to reference specific sheets in other code. For example, I can't use Sheet7.Activate, I can't use Sheets(7).Activate and I can't use Sheets("WhateverName").
3. I have some formulas that calculate a row number based on their sheet number (I'm not positive the SHEET() formula refers to the code name or just position of the sheet from left to right in the workbook). EX:
   • So this cell on Sheet6 grabs the contents of cell O2 on the Stock Query sheet, Sheet 7 would get O3... and so on.
   

   Please Login or Register  to view this content.

4. Finally, I know its unlikely that I would reach it, but there is an upper limit of sheets allowed in the VBA editor.

Actually, it looks like I can still refer to specific sheets using Sheets(i). That gave me an idea. I just removed that section of the code from the For loop that creates the new sheets and put it into its own loop at the end with an error handeler, and that seems to have fixed the problem *fingers crossed*. Here is my code now:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks for the suggestions! I knew the first one (I don't know why I was activating the sheet before I deleted it), but I didn't know the second one!