Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

Delete all sheets except defined ones

I need to run a macro that will delete all sheets except those named..

"WEEK1", "WEEK2", "WEEK3", "WEEK4", "WEEK5", "MONTH", strCurrentContractors, strCreateReport, strExceptions, strExchangeRate, strSummary, strTemplate

Regards,
Antonio King
0
antonioking
Asked:
antonioking
  • 2
1 Solution
 
StephenJRCommented:
Like this perhaps:
Sub x()

Dim ws As Worksheet

For Each ws In Worksheets
    Select Case ws.Name
        Case "WEEK1", "WEEK2", "WEEK3", "WEEK4", "WEEK5", "MONTH", _
             strCurrentContractors, strCreateReport, strExceptions, strExchangeRate, _
             strSummary, strTemplate
            ' do nothing
        Case Else
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
    End Select
Next ws

End Sub

Open in new window

0
 
antoniokingAuthor Commented:
Thanks, how do I stop excel asking for comfirmation to delete the sheet?
0
 
StephenJRCommented:
Line 12 should do that. Are you saying you get the message every time?
0
 
cbsbutlerCommented:
I left those lines out! Added in now and working. Thanks v much!

Antonio King
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now