?
Solved

Need a VBA macro to delete multiple sheets at once

Posted on 2013-11-06
4
Medium Priority
?
8,370 Views
Last Modified: 2013-11-07
Hello,

I need your help with a VBA macro that would delete all sheets in a workbook except SheetA, SheetB, SheetC and SheetD.  Is this possible?
0
Comment
Question by:mldaigle1
4 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 336 total points
ID: 39627101
this should be OK...
Sub DeleteSheets()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name = "SheetA" _
        Or ws.Name = "SheetB" _
            Or ws.Name = "SheetC" _
                Or ws.Name = "SheetD" _
                    Then
                        'do nothing
                    Else
                        ws.Delete
    End If

Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 332 total points
ID: 39627127
Hi,

a shorter version of Barman's code
Sub DeleteSheets1()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name Like "Sheet[A-D]" Then
        'do nothing
    Else
    ws.Delete
    End If

Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 332 total points
ID: 39627395
If the macro can reside on a different workbook then you can do this without looping

Sub delshts()
    Set owb = ActiveWorkbook
    opath = owb.Path
    oname = owb.Name
    Sheets(Array("SheetA", "SheetB", "SheetC", "SheetD")).Move
    owb.Close False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs opath & "/" & oname
    Application.DisplayAlerts = True
End Sub
0
 

Author Closing Comment

by:mldaigle1
ID: 39630813
Wow

I tested each of you solution the way you suggested and it work perfectly
Thanks!!
:)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
I came across an unsolved Outlook issue and here is my solution.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question