PDA

View Full Version : Delete named Sheets if they exist



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

gcomyn
07-19-2010, 09:43 AM
well... szNames is an array, so you need to show it as one...


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







or something like that


GComyn
:beerchug:

CurtR
07-19-2010, 10:01 AM
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
07-19-2010, 10:44 AM
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.



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

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