shabangle

New Member
Joined
Aug 9, 2013
Messages
23
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
Sheets(Sheets("2_Weightings").Index-v).Select

PS You don't need to select a sheet to delete it.
Code:
Application.DisplayAlerts = False
Sheets(Sheets("2_Weightings").Index-v).Delete
Application.DisplayAlerts = True
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
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...

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

Also Selecting isn't usually he best way..

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

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
Is there an echo in here? :biggrin:

Sorry, I was too slow...
 

shabangle

New Member
Joined
Aug 9, 2013
Messages
23
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
When you delete a sheet Excel will pop up a confirmation dialog, turning DisplayAlerts off prevents that happening.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top