+ Reply to Thread
Results 1 to 4 of 4

Delete Worksheets Based on Name from InputBox Criteria

Hybrid View

  1. #1
    Registered User
    Join Date
    12-19-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    32

    Delete Worksheets Based on Name from InputBox Criteria

    Hi Everyone,
    Is there a way to enter a word in an inputbox and have a macro delete all worksheets containing that word?

    I have the macro below but I don't know how to incorporate the InputBox function to ask the user to enter the criteria for deletion.

    For example, I should be able to enter Sales, and the macro will delete worksheet sales, sale2, and salesreport from my workbook...

    Option Explicit
    
    Sub Sample()
        Dim ws As Worksheet
    
        For Each ws In ThisWorkbook.Sheets
            If ws.Name Like "Mgt Report as at" & "*" Then
                '~~> This check is required to ensure that you don't get an error
                '~~> if there is only one sheet left and it matches the delete criteria
                If ThisWorkbook.Sheets.Count = 1 Then
                    MsgBox "There is only one sheet left and you cannot delete it"
                Else
                    '~~> This is required to supress the dialog box which excel shows
                    '~~> When you delete a sheet. Remove it if you want to see the
                    '~~~> Dialog Box
                    Application.DisplayAlerts = False
                    ws.Delete
                    Application.DisplayAlerts = True
                End If
            End If
        Next
    End Sub

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,263

    Re: Delete Worksheets Based on Name from InputBox Criteria

    Dim strN As String
    strN = Inputbox("What word?")
     If ws.Name Like "*" & strN & "*" Then
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Delete Worksheets Based on Name from InputBox Criteria

    Option Explicit
    
    Sub Sample()
        Dim ws As Worksheet, sTxt As String
    
    If ThisWorkbook.Sheets.Count = 1 Then
        MsgBox "There is only one sheet left and you cannot delete it"
        Exit Sub
    End If
    
    sTxt = Application.InputBox("Enter the Worksheet CONTAINS Text", "Text Required", , , , , , 2)
    
    Application.DisplayAlerts = False
    
    With ThisWorkbook
        For Each ws In .Sheets
            If InStr(ws.Name, sTxt) Then ws.Delete
            If .Sheets.Count = 1 Then Exit Sub
        Next
    End With
        
    Application.DisplayAlerts = True
    
    End Sub


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    12-19-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Delete Worksheets Based on Name from InputBox Criteria

    Thank you so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] delete row based on criteria - help!
    By pinky24 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-18-2014, 11:14 AM
  2. Delete Row based on Criteria!!
    By vignesh rocks in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2012, 07:30 AM
  3. Delete Worksheets Based On Name
    By Fermented Reptile in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2012, 11:02 AM
  4. Delete rows. Inputbox multiple criteria
    By vbanooby in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2011, 09:25 AM
  5. Delete specific rows, creating range and criteria using inputbox
    By gmccray in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2008, 02:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1