Remove all Duplicate Tags from Sheet names

dstrickland91

New Member
Joined
Jun 12, 2015
Messages
19
Hi all,

Yesterday I received help on here with a piece of code that inserts a letter before all my existing sheet names. I typically have 26 sheets in a file named with a number that represent the amount of records in that sheet. So before I run the code that inserts the letter, many of the sheets have the same amount of records so the sheet name will be something like 76(2).

Due to me running the code that inserts a letter into the sheet name, they no longer have duplicate names afterwards, so the duplicate (2) or (3) is no longer needed, but often after I insert the letter, 15-20 of the sheets will have a (2),(3) and so on after them. Is there anyway to have a code that goes through and removes all the (2) or (3)s in the sheetnames with VBA so I dont have to click each sheet name and delete manually?

Example

A76
B54
C76(2)
D76(3)
E84
F77
G84(2)

Afterwords would be

A76
B54
C76
D76
E84
F77
G84

I appreciate any help. Thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
The following code will do this

Sub delete1A()
Dim ws As Worksheet
For Each ws In Worksheets
Application.DisplayAlerts = False
If ws.Name Like "*(2)" Or ws.Name Like "*(3)" Then ws.Delete
Next ws
Application.DisplayAlerts = True
End Sub
 
Upvote 0
Hi Trevor,

Thanks for the response. This is actually deleting the sheet itself. Im looking to only delete the (2) from the sheet name. So if the sheetname is B80(2), Id like the code to remove to the (2) for the result to have the sheetname B80.

Am I explaining this to where it makes sense?
 
Upvote 0
Code:
Sub delete()
    Dim wks As Worksheet
    
    For Each wks In ThisWorkbook.Worksheets
        If wks.Name Like "*(?)" Then
            wks.Name = Left(wks.Name, Len(wks.Name) - 3)
        End If
    Next
End Sub
 
Upvote 0
This does the trick sort of. For some odd reason it works on some files but not on others? Any clues?
Change Thisworkbook to Activeworkbook and run the code with the workbook you want to process being the active workbook.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top