Subscript Out of Range Error '9

[EverClear]

Hi there!!!

I am creating spreadsheets for several cost centers. Each cost center has a number of departments. Some cost centers have 2 or 3 departments; other cost centers have as many as 15 departments.

I have a macro that will automatically add 3 worksheets in the workbook for each *department.* So if a cost center has 3 departments, then 3 x 3 = 9 worksheets will be added to the workbook. If there are 15 departments, then there'll be 15 x 3 = 45 worksheets, etc. (I use a 'Select Case' for this, and it works fine)

One part of the macro calls a number of subroutines to log-in to a database (SmartView) and retrieve the data to populate each of the worksheets (except the first 2 sheets, Main Menu and Control Sheet).

The PROBLEM:
Not all of the worksheets will have data, so I have additional code that will cycle through each of the spreadsheets again, only this time it checks Cell "D8" and if that cell is blank, the entire spreadsheet is deleted.

It works great as long as there are only 6 or 7 spreadsheets in the file. If there are more than 10 or so, the code bombs out with a 'subscript out of range 9' error. I need this macro to successfully loop through all of the templates no matter how many there are. Here's the code I have:</SPAN>
Sub RetrieveSmartView()</SPAN>

Dim i As Long</SPAN>

Call SVConnect </SPAN>'prompts user for database log-in</SPAN>

Call DeptZoom </SPAN>'once logged in, cycles through all the sheets & retrieves data by department</SPAN>

Call ProjectZoom </SPAN>'cycles through all the sheets & retrieves data by project</SPAN>

Call AccountZoom </SPAN>'cycles through all the sheets & retrieves data by account</SPAN>

Call AddRollupNodes </SPAN>'cycles through all the sheets & adds subtotals</SPAN>

Call RefreshAll </SPAN>'cycles through all the sheets & does one final retrieve of the data, w/ the subtotals
</SPAN>
'the first 2 sheets are my Main Menu and my Control Sheet. So I begin the loop with the third sheet:</SPAN>
For i = 3 To Sheets.Count
Sheets(i).Select </SPAN> 'this is where it bombs out after cycling through about 10 sheets successfully.</SPAN>
Call DisConnectSV </SPAN>'logs user off of the database</SPAN>

application.DisplayAlerts = False
If ActiveSheet.Cells(8, 4).Value = "" Then
Sheets(i).Select
ActiveWindow.SelectedSheets.Delete
End If
application.DisplayAlerts = True

Next i

PLEASE HELP!!!! </SPAN></SPAN>

 

[Andrew Poulsom]

You need to loop backwards if you are deleting:

For i = Sheets.Count To 3 Step -1

 

[BobUmlas]

Change
For i = 3 To Sheets.Count
to
For i = Sheets.Count to 3 Step -1

because say there are 6 sheets. Sheets.Count is 6. But when you delete a sheet, there are now only 5 sheets and Sheets(i) will not exist when you get to i being 6.

 

[Warship]

Since you're deleting sheets the sheet count is changing so after deleting the first few sheets the loop is looking for sheet indexes that no longer exist.
try looping backwards...

For i = Sheets.Count To 3 Step -1

 

[EverClear]

No way!!!! So simple, and yet it was driving me nuts!! Thanks for the responses, and the explanation. Now I'll understand the logic for next time.

THANK YOU ALL SOOO MUCH - THIS SITE IS AWESOME!!!