1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help to delete multiple sheets

Discussion in 'VBA Macros' started by mohan08, Jul 17, 2017.

  1. mohan08

    mohan08 New Member

    Messages:
    15
    Hi All,

    Help required to create macro to delete all sheets excepts on the one sheet which is mentioned via input box. I have tried with the below macro but getting the error message "Run-Time error '438"

    Code (vb):

    Sub retain()
    Dim todelete As Worksheet
    Dim Filetodelete As String
    Filetodelete = InputBox("Enter file name")

    For Each todelete In Application.ActiveWorkbook.Worksheets
    If todelete <> "filetodelete.value" Then
    todelete.Delete
    End If
    Next
    End Sub

     
  2. YasserKhalil

    YasserKhalil Active Member

    Messages:
    719
    Hello Mohan
    Try this code
    Code (vb):
    Sub Retain()
        Dim todelete        As Worksheet
        Dim filetodelete    As String

        filetodelete = InputBox("Enter Worksheet Name")

        Application.DisplayAlerts = False
            For Each todelete In Application.ActiveWorkbook.Worksheets
                If todelete.Name <> filetodelete Then
                    todelete.Delete
                End If
            Next todelete
        Application.DisplayAlerts = True
    End Sub
  3. mohan08

    mohan08 New Member

    Messages:
    15
    Thanks a ton Yasser, Can you please help me with combining the below code for unhiding all the worksheet and delete the sheets.

    Code (vb):


    Sub UnhideAllSheets()

    'Unhide all sheets in workbook.

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

    ws.Visible = xlSheetVisible

    Next ws

    End Sub

     
  4. YasserKhalil

    YasserKhalil Active Member

    Messages:
    719
    Do you mean like that
    Code (vb):
    Sub Retain()
        Dim todelete        As Worksheet
        Dim filetodelete    As String

        filetodelete = InputBox("Enter Worksheet Name")

        Application.DisplayAlerts = False
            For Each todelete In Application.ActiveWorkbook.Worksheets
                todelete.Visible = xlSheetVisible
                If todelete.Name <> filetodelete Then
                    todelete.Delete
                End If
            Next todelete
        Application.DisplayAlerts = True
    End Sub
    Thomas Kuriakose and mohan08 like this.
  5. mohan08

    mohan08 New Member

    Messages:
    15
    Thanks as lot Yasser:)
    YasserKhalil likes this.
  6. YasserKhalil

    YasserKhalil Active Member

    Messages:
    719
    You're welcome. Glad I can offer some help

Share This Page