CurtR
07-19-2010, 08:53 AM
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...... :banghead:
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
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
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...... :banghead:
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
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