Easy way to delete Tabs

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
Hi all,

I was just wondering if anyone new a macro that deleted specific tabs based on part their name. I have a macro that creates new tabs, but every tab has "Sheet 1" in it, the later tabs have "Sheet 1 (2)", "Sheet 1 (3)", etc. I just need those tabs to be deleted at the push of a button. I can explain more if someone needs it.

thanks in advance for the help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,894
Office Version
365
Platform
Windows
How about
Code:
Sub ERed1()
   Dim ws As Worksheet
   Application.DisplayAlerts = False
   For Each ws In Worksheets
      If ws.Name Like "*(*)" Then ws.Delete
   Next ws
   Application.DisplayAlerts = True
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,811
Office Version
365
Platform
Windows
This code will do that:
Code:
Sub DeleteSheets()
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If Left(ws.Name, 7) = "Sheet 1" Then ws.Delete
    Next ws
    Application.DisplayAlerts = True
End Sub
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
So I implemented this into my code, and I got it to delete "Sheet 1 (2)", but it isn't deleting "Sheet 1". So Here is what my code looks like with yours in it.
Code:
 Application.DisplayAlerts = False
   Sheets(Array("Merged", "Cat", Dog", "Plant", "Lizard", "Frog")). _    Select
    ActiveWindow.SelectedSheets.Delete
    
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name Like "*(*)" Then ws.Delete
    Next ws


Application.DisplayAlerts = True
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,811
Office Version
365
Platform
Windows
Did you try my code?
I tested it out, and it will delete any Sheet name that starts with "Sheet 1".
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,894
Office Version
365
Platform
Windows
Try Joe's code, I misunderstood & thought you only wanted to delete the sheets with (1) etc, rather than all sheets that contained Sheet1 in the name
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
So I tried both of your code, and Fluff's cleared the ones after, while Joe's cleared the first sheet, so I just put both of those in there and it works great that way.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,811
Office Version
365
Platform
Windows
So I tried both of your code, and Fluff's cleared the ones after, while Joe's cleared the first sheet, so I just put both of those in there and it works great that way.
I created sheets named "Sheet 1", "Sheet 1 (2)", and "Sheet 1 (3)", like shown in your example, and it deleted all of them.
If my original code, as-is, did not do that for you, then I suspect that your sheets are really not named the way you first said in your original post.
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
So I just noticed this; they are named "Sheet1". There isn't a space in there.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,894
Office Version
365
Platform
Windows
In that case use
Code:
If Left(ws.Name, 6) = "Sheet1" Then ws.Delete
 
Last edited:

Forum statistics

Threads
1,085,905
Messages
5,386,697
Members
402,011
Latest member
yousraemara

Some videos you may like

This Week's Hot Topics

Top