Saturday, 20 September 2014

How to Delete Specific or Unwanted Sheets from a Workbook with Excel Macro

Macro To Delete Specific or Unwanted Sheets from a Workbook 
'Suppose in your workbook you have some sheets  as shown below :
"MyReports","My_Links", "Sheet1","SLA_Report","Sheet2", "Calls_Info", "Files_Info", "Sheet3","Control_Panel"

Among these sheets if you want to Keep only "MyReports","My_Links", "SLA_Report", "Calls_Info", "Files_Info", "Control_Panel" Sheets and delete rest all sheets.

To do this task we can use the following Macro with Case Statement.

Public Sub Delte_UnWantedSheets()

    Dim WS_Name As String
    Dim WB_Main As Object
    Dim WS As Worksheet
    
    Set WB_Main = ThisWorkbook

  For Each WS In WB_Main.Sheets
        WS_Name = WS.Name
        
   Select Case WS_Name
        
        Case "MyReports" 
        Case "My_Links"
        Case "SLA_Report"
        Case "Calls_Info"
        Case "Files_Info"
        Case "ControlPanel"
        
   Case Else 'Other than any sheet delete

            Application.DisplayAlerts = False
            WB_Main.Sheets(WS_Name).Delete 
            Application.DisplayAlerts = True

    End Select
        
 Next

 End Sub

-----------------------------------------------------------------------------------------------------------------------

If you want to Delete the specific sheets from the above example , we can use the following Macro.

Method - II :
 Public Sub Delete_SpecificSheets()

    Dim WS_Name As String
    Dim WB_Main As Object
    Dim WS As Worksheet
    
    Set WB_Main = ThisWorkbook
 For Each WS In WB_Main.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

    Application.DisplayAlerts = False
            WS.Delete
    Application.DisplayAlerts = True
   
 Next

 End Sub

-----------------------------------------------------------------------------------------------------------------------

How to Call this Macro in Workbook Event : Workbook_Open()
Be Cautious that the Event will occur on Opening of the Workbook, You may lost your data sheets if wrongly use this event.

Private Sub Workbook_Open()
Call Delte_UnWantedSheets
End Sub





No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts