PDA

View Full Version : Solved: Delete sheets



av8tordude
01-21-2012, 01:16 PM
I have a userform (see attached) with the following: cboName, cboYear, and cmdEnter and I used to have tabs named in 4-digit year (i.e. 2010, 2011). The code in the cmdEnter button deletes any sheet tab older than than 3 years from the current year; (in this example, sheet 2010 would be deleted).

I've added names and put parenthesis around the year on each sheet tab [i.e. John Smith (2011), Jane Doe (2011)]

If "John Smith" is selected in cboName and "2012" selected in cboYear, then, delete any sheet older than 2 years with "John Smith name attached (in this case (John Smith (2010) should be deleted).

The only sheets left should be John Smith (2011), Jane Doe (2011), Jane Doe (2010)

Can someone assist with editing this code? Thank you for your help

mdmackillop
01-21-2012, 03:12 PM
Private Sub cmdEnter_Click()
Dim sh As Worksheet, nm As String, Yr As Long, s
Dim Age As Long

Age = 2 '<adjust to suit
Application.DisplayAlerts = False

For Each sh In ActiveWorkbook.Sheets
s = Split(sh.Name, "(")
nm = Trim(s(0))
Yr = Trim(Split(s(1), ")")(0))
If nm Like cboName And CLng(cboYear) - Yr > Age Then sh.Delete
Next
Application.DisplayAlerts = True

End Sub

av8tordude
01-21-2012, 04:52 PM
Hi Mack,

I'm getting an error on this line...Yr = Trim(Split(s(1), ")")(0)).

I have a feeling its because I have 2 other sheets that are not formatted like name (year). How can I ignore those 2 sheets?

mdmackillop
01-21-2012, 05:10 PM
The code depends upon a "(" in the sheet name, test for this using InStr in an If statement.

av8tordude
01-21-2012, 05:24 PM
Forgive my lack of intelligence, but can you help me out with an example?

av8tordude
01-21-2012, 05:32 PM
I got it Mack..Thank you for your help

If sh.Name Like cboName & " (20##)" Then

mdmackillop
01-22-2012, 05:21 AM
I was thinking of this, but whatever works is OK. You need to be careful in your solution that the space exists before the bracket in all sheet names.

If InStr(1, sh.Name, "(") > 1 Then
'rest of code
End If

av8tordude
01-22-2012, 01:45 PM
Thanks Mac