Delete worksheet if name isn't found in a cell in column D
I asked a question earlier about a worksheet_change macro I was working on. It's almost complete, but now I'm stumped. I'm trying to loop through all the worksheets in the workbook, and if the worksheet's name is not found in any cell in range D6:D34, I want to delete the worksheet. How can I write this? I'm completely stumped. Current code: Private Sub WorkSheet_Change(ByVal Target As Range) Application.ScreenUpdating = False 'Run faster Application.DisplayAlerts = False 'Just in case 'To add worksheets automatically Dim shtName As Variant For Each shtName In Sheets(1).Range("D6:D34") If shtName <> "" Then If WorksheetExists((shtName)) Then 'do nothing Else ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = shtName Sheets("Admin").Select End If Else 'there's no sheet End If Next 'to delete sheets with no matching value Dim ws_count As Integer Dim i As Long ws_count = ActiveWorkbook.Worksheets.Count For i = 1 To ws_count 'what do I need here??? Next i Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Function WorksheetExists(sName As String) As Boolean WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)") End Function
Dim ws As Worksheet ws_count = ActiveWorkbook.Worksheets.Count For i = ws_count To 2 Step -1 Set ws = ActiveWorkbook.Worksheets(i) If IsError(Application.Match(ws.Name,Sheets(1).Range("D6:D34"),0)) then Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True End If Next i
How do I autofill code in VBA?
Use excel VBA to update master list based on user input data
Change number format of a cell which inlude currecny using visual basic
Access reading Excel VBA date in US format
Changing a file name and path in a formula (within a macro) to user-defined
Excel conditional formating - conditional on sum of all previous cells and total sum
Consolidating single rows of data from multiple sheets into one - looping the macro?
Excel FInd and Replace Regex Macro
Excel VBA Loop function for all cells
Excel/VBA uncheck checkboxes if madatory field is empty
Excel VBA continually returning 0
Excel VBA: unable to disable DisplayAlert during drag+drop?
How to deal with Cyrillic letters caused error (#REF) in Worksheets (Unicode VBA display and handling), in VBA
Test for drive presence in an Excel 2007 macro before saving
Get all innertext VBA
Excel VBA Autofill Destination