Results 1 to 3 of 3

Thread: Deleting multiple sheets with VBA

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    370
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Deleting multiple sheets with VBA

    Hi

    I'm trying to delete 30 sheets from a spreadsheet with VBA.

    The spreadsheet has about 50 sheets, but I only want to delete the ones with the word "auto" at the end of the sheet name.

    I've written this and it has cleared the contents of those sheets.

    But I want to delete them without prompts.

    Can anyone advise how I need to change this please?



    Code:
    Sub DeleteSheets()
    
    
         Dim sht As Worksheet
        
        For Each sht In ThisWorkbook.Worksheets
        
            If Right(sht.Name, 4) = "auto" Then
                Application.DisplayAlerts = False
                sht.Cells.Delete
                Application.DisplayAlerts = True
              
            End If
        Next
    
    
    Exit Sub
    
    
    
    
    End Sub

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    629
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple sheets with VBA

    Hi there. You were nearly there!

    Try this:
    Code:
    Sub DeleteSheets()
    
    
         Dim sht As Worksheet
        
        For Each sht In ThisWorkbook.Worksheets
        
            If Right(sht.Name, 4) = "auto" Then
                Application.DisplayAlerts = False
                sht.Delete
                Application.DisplayAlerts = True
              
            End If
        Next
    
    End Sub
    PS You dont need the exit sub, as the very next line is the end of the sub anyway.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    Board Regular
    Join Date
    Nov 2016
    Posts
    370
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting multiple sheets with VBA

    Ok, awesome!

    That worked!

    Thank you jmacleary!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •