excel


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

Related Links

How do I compare two columns in different sheets for non existing values and then copy them to the main sheet?
Is There a Way to Pull Individual Rows from Multiple Sheet Excel
Loop through all the directories and sub-directories with intermediate wildcard patterns
Extract gz and convert csv to xlsx
How do I concatenate cells and ignoring empty cells
Merging two spreadsheets using a script and creating HTML language
Excel 2016 crash on API timer initiated Workbook.close
Deleting and adding items in a table Excel VBA
Excel: calculate modulus of a very large number without getting overflow error
how to use the vb code for the entire column based on the condition
Using formula to update another cell? [closed]
VBA define range value as value of a cell
Stumped: ExcelVBA RowSource for a ComboBox
Excel - VBA - Access: Date Selection Solution
How to change values in spesific cells after copying VBA
UserForm internal counter for calculations

Categories

HOME
menu
automation
refactoring
umbraco7
amazon-redshift
castle-windsor
houndify
filesystems
sublimelinter
visual-foxpro
javacard
chapel
codeception
flannel
shoutem
access-control
httpresponse
emu8086
google-fusion-tables
markup
clang-format
code-injection
robocopy
filenames
hal
fxml
pgadmin
grunt-connect-proxy
google-tv
filestream
james
webex
cachemanager
itunes-store
slam
pwd
api-manager
cakephp-3.3
same-origin-policy
sceditor
itil
ldflags
agile-project-management
arithmetic-expressions
stl-format
sonarqube-5.4
splash-screen
listboxitem
mindbody
eclipse-emf-ecore
custom-taxonomy
du
gstat
openembedded
wave
er-diagrams
rich-snippets
geographical-information
zooming
review
mooc
string-search
mux
controltemplate
apparmor
bonjour
jquery-mobile-collapsible
jax-rpc
converters
springmockito
custom-view
in-app
registrar
css-parsing
google-instant-previews
smalldatetime
tao
mahjong
context-sensitive-help
stack-unwinding
asp.net-mvc-2-validation
pvcs

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App