VBA to delete sheets loop

[shabangle]

Hi All,

I need a script that will delete sheets based on an earlier calculation. TabDiff2 is defined earlier.

I have the following code which is breaking:

For v = TabDiff2 To 1 Step -1
Sheets("2_Weightings").Index -v.Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Next v

Is there a problem with selecting sheets using their index dynamically?

How else could I do this?

Thanksfor any help,
Shabangle

 

[Norie]

If you wanted to select a sheet by index based on the index of the sheet '2_Weightings' and the value of v you would need something like this.

Sheets(Sheets("2_Weightings").Index-v).Select

PS You don't need to select a sheet to delete it.

Application.DisplayAlerts = False
Sheets(Sheets("2_Weightings").Index-v).Delete
Application.DisplayAlerts = True

 

[gallen]

The index just returns an integer so in essence you are trying to select the integer which isn't an object.

This is untested so try on a copy...

Sheets(Sheets("2_Weightings").Index - v).Select

Also Selecting isn't usually he best way..

    For v = TabDiff2 To 1 Step -1
        Application.DisplayAlerts = False
        Sheets(Sheets("2_Weightings").Index - v).Delete
        Application.DisplayAlerts = True
    Next v

 

[gallen]

Is there an echo in here?

Sorry, I was too slow...

 

[shabangle]

Thanks muchly for both replies.

Out of curiosity what is the application display alerts toggle doing? Would an alert be triggered by this operation?

 

[Norie]

When you delete a sheet Excel will pop up a confirmation dialog, turning DisplayAlerts off prevents that happening.