using VBA to delete sheets which have a specific string

Question

I am currently learning VBA.

I am watching this series of tutorial videos on youtube regarding how to use VBA to use a loop and if statement to create dupes of my sheets and how to delete them.

I changed my code to the following:

Sub deleteloop()       
    Dim x As Worksheet    
    Dim y As String
    Dim z As String

    Application.DisplayAlerts = False

    For Each x In ActiveWorkbook.Sheets        
        y = x.Name     
        z = VBA.right(y, 3)       
        If z = "(2)" Then
            ActiveSheet.delete           
        End If
    Next x

    Application.DisplayAlerts = True
End Sub

As you can imagine, my duplicated sheets have "(2)" at the end of their string, so the idea is for my code to delete any duplicated sheets using this.

The problem is that it deletes all the duplicated sheets but the last one, and I have to run it again to delete the last remaining sheet with "(2)" at the end.

What am I doing wrong here?

CLR · Accepted Answer · 2019-02-08 14:48:19Z

3

Change ActiveSheet.delete to x.delete as you want to delete the sheet you're examining, not the one that's currently active.

answered Feb 8, 2019 at 14:48

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using VBA to delete sheets which have a specific string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related