Macro: select all tabs between two bounds
Solved
Frantoc37
Posted messages
17
Status
Membre
-
Theo.R Posted messages 585 Status Membre -
Theo.R Posted messages 585 Status Membre -
Hello,
I have an Excel file with 2 sheets titled "Borne 1" and "Borne 2".
I would like to create a macro that selects all the sheets (regardless of their number) between these two boundaries in order to delete them.
Can someone help me?
Thank you :-)
I have an Excel file with 2 sheets titled "Borne 1" and "Borne 2".
I would like to create a macro that selects all the sheets (regardless of their number) between these two boundaries in order to delete them.
Can someone help me?
Thank you :-)
3 réponses
Hello,
For this, you need to proceed not by the tab name but by its positioning.
In the code I am proposing, "Borne 1" is the 1st tab and "Borne 2" is the 9th tab. You just need to change the variables a and b to match your document (a = number "Borne 1" + 1; b = number "Borne 2" - 1).
For this, you need to proceed not by the tab name but by its positioning.
In the code I am proposing, "Borne 1" is the 1st tab and "Borne 2" is the 9th tab. You just need to change the variables a and b to match your document (a = number "Borne 1" + 1; b = number "Borne 2" - 1).
Sub test()
a = 2
b = 8
For i = b to a step -1
sheets(i).select
ActiveWindow.SelectedSheets.Delete
Next i
End sub
Thank you, Théo, for your quick response.
I just tested your macro, and it works very well...
.. but...
The number of tabs that fall between threshold 1 and threshold 2 changes regularly. (Some days I have 15 tabs to process, other days 25...) The "numerical" position of threshold 2 is therefore not always the same... I can't always assign it the position "9".
The macro I would like to implement will delete all the tabs between threshold 1 and 2 to then import other tabs to process.
PS: Threshold 1 is fixed (so no problem with this tab...)
I just tested your macro, and it works very well...
.. but...
The number of tabs that fall between threshold 1 and threshold 2 changes regularly. (Some days I have 15 tabs to process, other days 25...) The "numerical" position of threshold 2 is therefore not always the same... I can't always assign it the position "9".
The macro I would like to implement will delete all the tabs between threshold 1 and 2 to then import other tabs to process.
PS: Threshold 1 is fixed (so no problem with this tab...)
The following code should work, you just need to name the reference sheets respectively "Borne 1" and "Borne 2" (don't forget the space).
Sub test()
Sheets("Borne 1").select
a = ThisWorkbook.Worksheets.Count
Sheets("Borne 2").select
b = ThisWorkbook.Worksheets.Count
For i = b to a step -1
sheets(i).select
ActiveWindow.SelectedSheets.Delete
Next i
End sub
Sorry, I had replied this morning but my internet must have crashed because I see that my response was not published..
Anyway, my macro needs to delete the tabs between your boundaries and the boundaries themselves.
If that's not the case, is it possible to get a document via www.cjoint.com please?
Best regards,
Anyway, my macro needs to delete the tabs between your boundaries and the boundaries themselves.
If that's not the case, is it possible to get a document via www.cjoint.com please?
Best regards,