Why not use the index number?

idx = ActiveSheet.Index
Worksheets(idx).Delete

You can substitute your sheet name or number for "ActiveSheet", or if you
know the index number, just use the one line with worksheets() and fill in
the index number.

"ankur" wrote:

Hi All,


I have a strange problem (or may be I do not understand it well)


I am trying to run a code on the Before Save event which would delete
sheet named as todays date (The function here will convert date in a
string format t be saved) .


The problem is when I press the Alt Save button it runs fine and
deletes the sheet named "5-2-2007" but when I save the file using an
Activex Button that I created on the sheet it does't deletes the
sheet.


The Button has the code:


activeworkbook.save


following is the code I have .....


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)


Dim NameOfCurrentSheet As String


NameOfCurrentSheet = NameOfToday(Now())
Sheets(NameOfCurrentSheet).Delete


End Sub


Function NameOfToday(d As Date) As String
Day1 = Day(d)
Month1 = Month(d)
Year1 = Year(d)


NameOfToday = Day1 & "-" & Month1 & "-" & Year1
End Function


Thanking you all in advance.


Regards
Ankur Richard
www.xlmacros.com