How to delete worksheets with specific names only

Question

My Master Report in fold.xlsm has a range of file and corresponding worksheets with tab names that correspond to the client and the type of data on that tab (indicated by variables xWs_Tax, xWs_Ins, etc.) After storing these it then opens the corresponding client workbook.(replaced with fname.xlsx below) The code is supposed to delete all worksheets NOT matching these stored names that include the client number in them. But I can't seem to get it to work. Either it deletes all the tabs or it does nothing at all depending on how I fiddle with it. Does anything jump out at you below or am I using bad code maybe? Do I need to do more then just declare Dim xWs As Worksheet ?

Here is where the variables are stored:

Dim xWs_ins As String
Dim xWs_tax As String
Dim xWs_ucc As String
Dim xWs_loc As String

Dim rc As String
rc = Range("P40")

For i = 41 To (rc + 40)
 
   Workbooks("Master Report in fold.xlsm").Activate
   
       MsgBox$ Range("J" & i)
     xWs_ins = Range("J" & i)
     xWs_tax = Range("K" & i)
      xWs_ucc = Range("L" & i)
       xWs_loc = Range("M" & i)
  Workbooks.Open filename:= for example "20 Investor Certification - Master Servicers.xlsx 

Dim xWs As Worksheet
 ''    Application.ScreenUpdating = False
   '' Application.DisplayAlerts = False
    For Each xWs In Application.ActiveWorkbook
        If xWs.Name <> xWs_ins And xWs.Name <> xWs_tax And xWs.Name <> xWs_ucc And xWs.Name <> xWs_loc Then
            xWs.Delete
        End If
    Next
 ''   Application.DisplayAlerts = True
  ''  Application.ScreenUpdating = True

ActiveWorkbook.Save
           
   Next i

So after the code runs, each workbook (file name) should have only those corresponding 4 tabs left in it. — ORLANDO VAZQUEZ
What is the name of the workbook and worksheet where this rc = Range("P40") is happening and what is it all about? What is the name of the workbook containing this code? What is the name of the worksheet in workbook Master Report in fold.xlsm? — VBasic2008
@Basic2008 The workbook that holds all the code is named "Master Report in fold.xlsm" That range is simply the count of the number of rows to iterate through. The example I gave only would show 2 for 2 rows. The worksheet in the workbook Master Report in fold.xlsm is Sheet1 btw, the worksheets that are being looked at are not in this master workbook, but rather the workbooks names in range in column i. — ORLANDO VAZQUEZ
In my solution, you can replace Workbooks("Master Report in fold.xlsm") with ThisWorkbook so you can e.g. rename the file as you wish. The rest should do fine. Your feedback is appreciated. — VBasic2008

VBasic2008 VBasic2008 · Accepted Answer · 2021-03-31T17:59:45

Delete Unwanted Sheets

There are unclear 'items' addressed in OP's comments and marked with ??? in the code.

Option Explicit

Sub deleteUnwanted()

    Dim swb As Workbook: Set swb = Workbooks("Master Report in fold.xlsm")
    ' The worksheet name is unknown??? ("Sheet1")
    Dim sws As Worksheet: sws = swb.Worksheets("Sheet1")
    Dim sCell As Range
    Dim srg As Range
    
    Dim dwb As Workbook
    Dim dsh As Object ' There is no 'Sheet' object.
    Dim dArr As Variant
    Dim dshCount As Long
    
    ' The workbook and worksheet are unknown??? (sws)
    Dim rc As String: rc = sws.Range("P40").Value
    
    Application.ScreenUpdating = False
    
    For i = 41 To (rc + 40)
        n = 0
        Set sCell = sws.Cells(i, "I")
        Set dwb = Workbooks.Open(sCell.Value)
        dshCount = dwb.Sheets.Count
        ReDim dArr(1 To dshCount)
        Set srg = sws.Columns("J:M").Rows(i)
        For Each dsh In dwb.Sheets
            If IsError(Application.Match(dsh.Name, srg, 0)) Then
                n = n + 1
                dArr(n) = dsh.Name
            End If
        Next
        If n > 0 Then
            ReDim Preserve dArr(1 To n)
            Application.DisplayAlerts = False
            dwb.Worksheets(dArr).Delete
            Application.DisplayAlerts = True
        End If
        dwb.Close SaveChanges:=True
    Next i

    Application.ScreenUpdating = True

End Sub

How to delete worksheets with specific names only

1 Answers

Delete Unwanted Sheets