Hi Guys,
I need urgent help with respect to the above
I need my code to look through values(whose values are coincidentally the same as some worksheet names) on a worksheet called "Summary" and then delete any visible worksheet that does not have its name equal to the values on the "Summary" worksheet. have got everything set up but it appears that my loop does not work accurately as it still does not delete some worksheets whose names are not values on the Summary tab.
Sub espada()
Dim x
Dim mth As String
Dim buk As Workbook
Dim ws As Worksheet
Dim lruw As Integer
Dim i As Integer
Dim y As Integer
Dim iya As Worksheet
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
Set buk = ActiveWorkbook
mth = MonthName(Month(Date))
On Error Resume Next
MkDir "H:\Management Information\CPPD Attendance Monitoring Template Archive"
Err.Clear
ChDir "H:\Management Information\CPPD Attendance Monitoring Template Archive\"
ActiveWorkbook.SaveAs Filename:="CPPD Attendance Monitoring_" & mth & " Archive"
Sheets("Summary").Activate
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$500").AutoFilter Field:=2, Criteria1:="YES"
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$1:$B$500").AutoFilter Field:=2
Selection.AutoFilter
ActiveCell.Offset(1, 0).Range("A1").Select
lruw = Worksheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Dashboard").Visible = xlSheetVeryHidden
Sheets("RAW DATA").Visible = xlSheetVeryHidden
For i = 2 To lruw
If ActiveWorkbook.Worksheets(i).Visible = True _
And ActiveWorkbook.Worksheets(i).Name <> Sheets("Summary").Range("A" & i) Then
' Sheets("Summary").Range("A" & i) = iya
Set iya = ActiveWorkbook.Worksheets(i)
iya.Delete
End If
Next i
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
Please help me out!!!
Bookmarks