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 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

Categories

HOME
logic
statistics
dependent-type
entities
uitableview
aws-codepipeline
wayland
google-cloud-endpoints
external
xhtml
distributed-database
ieee-754
openmeetings
bigdata
apostrophe-cms
treeset
log4net
axon
shogun
cllocationmanager
redactor
accelerometer
markup
react-boilerplate
gulp-sass
unimrcp
google-developers-console
entity-relationship
http-caching
nodemon
dsolve
data-migration
coupon
c#-to-f#
bluehost
word
lsmeans
gperftools
cassandra-3.0
android-debug
product-key
database-theory
dos
youtube-analytics
nerdtree
smartassembly
oracle-ebs
fixture
scalacheck
configuration-files
azure-sql-server
keyword-search
ios6
modelio
cakephp-2.x
mosync
inputstreamreader
opscenter
dispy
vorlon
apple-search-ads
rpy2
nslog
google-chartwrapper
svg-edit
python-wheel
metamodel
nested-function
dde
giflib
website-deployment
testdroid
openoffice-writer
iterm
jcarousel
android-6.0.1
swift-mt
realbasic
argo
jedit
moovweb
jaxb2-maven-plugin
maven-site-plugin
apache-wink
rot13
google-plugin-eclipse
xcode-instruments
textile
controltemplate
linq-to-lucene
complete.ly
apiaxle
system.speech.recognition
stackmob
kendo-tooltip
tandem
sniffing
iwebkit
objectiveflickr
mobile-phones
set-include-path
ant4eclipse
codehighlighter
mkstorekit
pvcs
astoria

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