Delete named Sheets if they exist

[CurtR]

Hi Everyone,
I'm new the community, I have spent most of the morning here reading and learning, I hope someday to be able to offer something back but right now I have been given a workbook to try to automate a little.
I am stumbling my way through this as I have not done VBA for a couple of decades now. What seems to be the simplest of the tasks I want to perform is giving me the greatest trouble......

Basically I need to check to see if sheet names exist and if so delete them.
The names are a string and date concatenated together.

I am getting a Type Mismatch Error at the start of the If Statement.
I have tried dimming the szNames as string and variant.
I have search the internet and my books for a clue, being able to find anything I am hoping someone here will be willing to help the confused..

Any help would be greatly Appreciated !

Thanks
Curt

[vba]Sub duplicate()
Dim ws As Worksheet
Dim szNames As Variant
szNames = Array("DataSheet- " & Format(Date, "mmm-dd-yy"), "MasterSheet - " & Format(Date, "mmm-dd-yy"))
For Each ws In ThisWorkbook.Worksheets
If ws.Name = szNames Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Exit Sub
End If
Next ws

End Sub[/vba]

[gcomyn]

well... szNames is an array, so you need to show it as one...

VBA:

[vba]
Sub duplicate()
Dim ws As Worksheet
Dim szNames As Variant
Dim x As Integer

szNames = Array("DataSheet- " & Format(Date, "mmm-dd-yy"), "MasterSheet - " & Format(Date, "mmm-dd-yy"))
For Each ws In ThisWorkbook.Worksheets
For x = 0 To UBound(szNames)
If ws.Name = szNames(x) Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Exit Sub
End If
Next x
Next ws
End Sub

[/vba]

or something like that

GComyn

[CurtR]

LOL...

Yup it was something like that!!

I just finished reading about the U and L bound functions. Things are a little foggy in the way that you used ubound, I see what you did and in the back of my mind I even understand what you did.. just don't ask me to explain it !

Thank you very much for taking time to help a stranger...
Curt

[Bob Phillips]

I am stumbling my way through this as I have not done VBA for a couple of decades now.

Then you haven't done VBA, as VBA didn't exist a couple of decades ago.

[vba]

Sub duplicate()
On Error Resume Next
ThisWorkbook.Worksheets ("DataSheet- " & Format(Date, "mmm-dd-yy")).Delete
ThisWorkbook.Worksheets ("MasterSheet - " & Format(Date, "mmm-dd-yy")).Delete
On Error Goto 0
End Sub
[/vba]

[CurtR]

Thanks for the Alt. code.

And your right I guess it would of been Lotus123 or one of the Lotus Smart Suite products we used under OS 2 before we went to Windows. No mater how you twist it, its been a long time.