Weird forloop behavior with worksheets

[mattreingold]

Hello everyone, thank you in advance for your help.

The weirdest thing is happening to me while trying to code this macro, I am trying to run a forloop to delete worksheets 'numberOfFiles' to 17.

'n...' is a variable which populates from user input. Essentially, the first sheet in this workbook is a collective table of all samples, sheet 2 is a cumulative chart of all samples (stress strain graphs), then sheet 3 is specific information on sample 1, sheet 4 is specific information on sample 2 and so on.

If there is only one sample, then, I want to delete sheets 3 thru 17, if there are 2 samples - 4 thru 17 and so on.

The problem is, when I run this forloop - it deletes all even sheets starting at Sheet4, not every sheet starting at Sheet4.

This, then, throws a subscript out of bounds error. Am I just missing something so simple here in my forloop indexing?

P.S. The second forloop deleting legend entries works fine.

Dim WBT As Workbook ' This Workbook
numberOfFiles = InputBox("Enter Number of Samples to Analyze")
Set WBT = Workbooks("RunCompressionTestingReport.xlsm")

...
Else If numberOfFiles = 2 Then
     With WBT
           For ytu = numberOfFiles + 3 To 17
                Sheets(ytu).Delete
           Next ytu
     End With
     With TotalCht.Chart
           For emg = 15 To numberOfFiles + 1
                .Legend.LegendEntries(emg).Delete
           Next emg
     End With
Else If numberOfFiles = 1 Then
     With WBT
           For ytu = numberOfFiles + 3 To 17
                Sheets(ytu).Delete
           Next ytu
     End With
     With TotalCht.Chart
           For emg = 15 To numberOfFiles + 1
                .Legend.LegendEntries(emg).Delete
           Next emg
     End With
End If

[mattreingold]

I actually just modified my code to:

Dim WBT As Workbook ' This Workbook
numberOfFiles = InputBox("Enter Number of Samples to Analyze")
Set WBT = Workbooks("RunCompressionTestingReport.xlsm")

...
Else If numberOfFiles = 2 Then
     With WBT
           For ytu = numberOfFiles + 3 To 17
                .worksheets(ytu).Delete
           Next ytu
     End With
     With TotalCht.Chart
           For emg = 15 To numberOfFiles + 1
                .Legend.LegendEntries(emg).Delete
           Next emg
     End With
Else If numberOfFiles = 1 Then
     With WBT
           For ytu = numberOfFiles + 3 To 17
                .worksheets(ytu).Delete
           Next ytu
     End With
     With TotalCht.Chart
           For emg = 15 To numberOfFiles + 1
                .Legend.LegendEntries(emg).Delete
           Next emg
     End With
End If

Using .worksheets instead of Sheets and now nothing deleted at all...

Very confused:/

[mattreingold]

I understand! When deleting sheets, after deleting "Sheet4", "Sheet5" becomes the fourth sheet, thus this code works:

           ElseIf numberOfFiles = 2 Then
                With WBT
                      For ytu = 1 To 13
                           Sheets(5).Delete
                      Next ytu
                End With
                With Chart1
                      For emg = 1 To 13
                           .SeriesCollection(3).Delete
                      Next emg
                End With
            ElseIf numberOfFiles = 1 Then
                With WBT
                      For ytu = 1 To 14
                           Sheets(4).Delete
                      Next ytu
                End With
                With Chart1
                      For emg = 1 To 14
                           .SeriesCollection(2).Delete
                      Next emg
                End With
            Else
...

[Paul_Hossler]

IMHO it's very risky to rely a worksheet's index since as you found they can change

It'd be better to determine a 'signature' on the sheets to delete and use that, or something else

[mattreingold]

Paul, I was originally just hardcoding it super badly with WBT.Worksheets("Sheet3").Delete and repeating the statement over 15 some lines for each iteration, I was just looking for a better way of doing so. This code is pretty consistent, it acts as a seed file so using the index shouldn't cause any problems necessarily, however, how would you suggest deleting them using this signature thing you mentioned?

[Paul_Hossler]

I just meant that (for example)

1. if A1 contains "LOCATION" and B1 contains "DATE", then the macro can loop and check A1 and B1 to decide if it can be deleted, or

2. if the WS names start with a consistent string or follow a pattern ("Month01", "Month02", …) -- same thing

[mattreingold]

Ah, I see.

I would have done something like that, however the way this macro works is it is a shell for data input. It can analyze up to 30 samples (has a sheet for each) and if someone only imports 5, I want it to delete the 25 following sheets.

I know this is probably the worst way to do this (adding sheets and formatting accordingly would probably be 100x more effective) however for the scope of this project and repetitious nature, it will suffice.

Also, the format of each sheet is highly complicated (in hindsight, probably should have made one sheet fully formatted and copied to a new one for each sample ).

All part of the learning curve for me lol.

Thank you for your guidance, though - I really appreciate your time.

[Aflatoon]

Also, as a general comment, when deleting it's better to work backwards (for the reason you encountered):

For ytu = 17 to numberOfFiles + 3 Step -1