Solved

Excel VBA- Delete WS based on DV List Value

Posted on 2013-01-24
3
404 Views
Last Modified: 2013-01-24
Hi Experts,  :)

I'm creating a workbook that auto adds worksheets and name them based on values within range A4:A23.  The user would click on the yellow image to have named sheet(s) created.  

Name Range Sample
I need VBA that'll delete worksheets whose name is in the DV list in cell A2.

Sample DV List
Is this possible?  Sample workbook is attached also

Thank you!
SampleDeleteWSBasedCriteria.xlsm
0
Comment
Question by:"Abys" Wallace
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
Shanan212 earned 500 total points
ID: 38815655
See code/attached worksheet.

Try clicking the button next to A2 to run code.

Sub deletesheet()
    
    Dim e As String, iRow As Long, ws As Worksheet, main As Worksheet
    
    Set main = ActiveSheet
    e = ActiveSheet.Range("A2").Value
    
    ActiveSheet.AutoFilterMode = False
    Range("A3:F3").Select
    Selection.AutoFilter
    
    iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
    
    If Application.WorksheetFunction.CountIf(Range("A5:A" & iRow), e) > 0 Then
        
        For Each ws In ActiveWorkbook.Worksheets
            ws.Activate
            If ws.Name = e Then
                Application.DisplayAlerts = False
                Sheets(e).Delete
            End If
        Next ws
        main.Select
        Range("A2").Select
        MsgBox "Deleted " & e, vbInformation
    Else
        MsgBox "Your selection is not found among the list!", vbExclamation
    End If
    
End Sub

Open in new window

SampleDeleteWSBasedCriteria.xlsm
0
 

Author Closing Comment

by:"Abys" Wallace
ID: 38815815
Shanan212:  Thank you!  works perfect!  and I like the msg box that advises which sheet was just deleted.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38815842
You are welcome :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now