Deleting multiple sheets with VBA

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
472
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
982
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,620
Messages
5,487,915
Members
407,614
Latest member
fuslela

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top