Results 1 to 4 of 4

Thread: [RESOLVED] Delete all worksheets except few specified worksheets from a workbook

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Resolved [RESOLVED] Delete all worksheets except few specified worksheets from a workbook

    I have got the below macro to delete all worksheets in a workbook except few specified ones, but it shows type mismatch error on the line highlighted in bold. I am using excel 2010 version.

    Code:
    Sub deleteshts ()
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet3" Or "Sheet8" Or "Sheet11" Or "Pivot" Then
    ws.Delete
    End If
    Next
    Application.DisplayAlerts = True
    End Sub

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Delete all worksheets except few specified worksheets from a workbook

    Code:
    Sub delSheets()
        Dim ws As Worksheet
        
        Application.DisplayAlerts = False
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "pivot" And ws.Name <> "Sheet3" Then  'add more as needed
                ws.Delete
            End If
        Next
        Application.DisplayAlerts = True
    End Sub

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Delete all worksheets except few specified worksheets from a workbook

    of you could use a select case

    Code:
        Dim ws As Worksheet
        
        Application.DisplayAlerts = False
        For Each ws In ThisWorkbook.Worksheets
            Select Case ws.Name
                Case "sheet3", "pivot", "sheet8"  ' add as required
                    ' do nothing
                Case Else
                    ws.Delete
            End Select
        Next
        Application.DisplayAlerts = True
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Delete all worksheets except few specified worksheets from a workbook

    Great. It works. thanks a lot

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width