Unprotect and Paste Values - errors if sheet deleted.

[mardeebo]

Acquired a workbook with this VBA code. The workbook has 25+ tabs, 5 of which are always named exactly the same but the remaining will contain something like "Day 1 XX YY" or "Day 2 YY ZZ". And, just for fun, sometimes its "XX YY Day 1" and "YY ZZ Day 2". In other words, the bulk of the tabs can have varying names in them but they do always have the word "Day" in it.

Sometimes the person doing the data entry deletes or renames a sheet and then when I run the macro (or, rather, a set of macros), it bombs out on the line WS.Select. I'd really like to rewrite this so it's not so fussy. How could I write this so it will do the steps for any worksheet containing the word "day" in the sheet name?

Sub UnprotectANDPasteValues()
Dim iCurWS As Integer
Dim WS As Worksheet


For iCurWS = 6 To Worksheets.Count - 1
    Set WS = Sheets(iCurWS)
    WS.Select
    ActiveSheet.Unprotect
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("T:T").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("W:W").Select
    Selection.Delete Shift:=xlToLeft
    Columns("Z:Z").Select
    Selection.Delete Shift:=xlToLeft

Next iCurWS

iCurWS = 6
Set WS = Sheets(iCurWS)

WS.Select

End Sub

 

[mrshl9898]

Updated, best not to select ether so removed that.

Sub UnprotectANDPasteValues()

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    If WS.Name Like "*Day*" Then
    WS.Unprotect
    WS.Cells.EntireColumn.Hidden = False
    WS.Cells.Copy
    WS.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    WS.Columns("T:T").Delete Shift:=xlToLeft
    WS.Columns("W:W").Delete Shift:=xlToLeft
    WS.Columns("Z:Z").Delete Shift:=xlToLeft
    End If
Next WS

End Sub