Deleting Multiple Worksheets (or tabs) from a Workbook

[jdurham67]

I pull data daily and the resulting workbook contains multiple tabs. I always want to delete all of them except the one named "Master Data." The number of tabs can vary, but the one that I want to keep will always have the same name.

Thanks in advance!


running Excel 2007 on Windows 7



- Posting guidelines, forum rules and terms of use

- Try searching for your answer first, see how

- Read the FAQs

- List of BB codes

 

[jbeaucaire]

This macro will make sure the active workbook has the sheet, then it will delete all the others.

Option Explicit

Sub KeepOneWS()
Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With ActiveWorkbook
    If Evaluate("ISREF('Master Data'!A1)") Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> "Master Data" Then ws.Delete
        Next ws
    Else
        MsgBox "MASTER DATA sheet not found in active workbook."
    End If
End With

Application.ScreenUpdating = True
End Sub

 

[jdurham67]

Thank you SOO much - worked perfectly!

John

 

[jaypatel]

Hi

Say if you needed to keep 3 sheets in total, say Sheet1, Sheet2 and Sheet3, what would the code be?

Cheers

Jay

 

[jbeaucaire]

Say if you needed to keep 3 sheets in total, say Sheet1, Sheet2 and Sheet3, what would the code be?

One way:

Option Explicit

Sub KeepSomeWS()
Dim ws As Worksheet
Dim ShtsKeep As String

ShtsKeep = "Sheet1,Sheet2,Sheet3"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    With ActiveWorkbook
        For Each ws In ActiveWorkbook.Worksheets
            If InStr(1, ws.Name, ShtsKeep) = 0 Then ws.Delete
        Next ws
    End With

Application.ScreenUpdating = True
End Sub

You might get unexpected leftovers if you were to keep, say, "Sheet11", a sheet called "Sheet1" would actually match and be kept. Hopefully you have better sheetnames than this.

 

[jaypatel]

Hi

Tried it on 2003 and 2007 versions, it comes up with a runtime error 1004 method delete of object worksheet failed.

any other ideas?

Cheers

Jay

 

[jbeaucaire]

Sorry, just got the parameters backwards...

Option Explicit

Sub KeepSomeWS()
Dim ws As Worksheet
Dim ShtsKeep As String

ShtsKeep = "Sheet1,Sheet2,Sheet3"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    With ActiveWorkbook
        For Each ws In ActiveWorkbook.Worksheets
            MsgBox InStr(1, ShtsKeep, ws.Name)
            'If InStr(1, ws.Name, ShtsKeep) = 0 Then ws.Delete
        Next ws
    End With

Application.ScreenUpdating = True
End Sub