I have the following on a button

Sub makesheet()
Sheets("Master").Select
Sheets("Master").Copy after:=Sheets("Master")
ActiveSheet.Name = Format(Now, "dd-mmm")
End Sub

Trouble is if the sheet already exists I get an error message. What I want
is if the worksheet with today's date already exists make it active. If it
does not exist create sheet.

Thanks Blue

You can use this function to let you know if a name has been used...

private function NameExists (byval strSheetName) as boolean
dim wks as worksheet
dim blnReturnValue as boolean

blnReturnValue = false

for each wks in worksheets
if wks.name = strsheetname then
blnReturnvalue = true
endif
next wks
namexists = blnreturnvalue
End function

Then you can modify your code as follows

Sub makesheet()
if namexists( Format(Now, "dd-mmm")) then
sheet(Format(Now, "dd-mmm")).select
else
Sheets("Master").Select
Sheets("Master").Copy after:=Sheets("Master")
ActiveSheet.Name = Format(Now, "dd-mmm")
endif
End Sub


This is off the top of my head so I hope it works for you...
"Blue" wrote:

I have the following on a button

Sub makesheet()
Sheets("Master").Select
Sheets("Master").Copy after:=Sheets("Master")
ActiveSheet.Name = Format(Now, "dd-mmm")
End Sub

Trouble is if the sheet already exists I get an error message. What I want
is if the worksheet with today's date already exists make it active. If it
does not exist create sheet.

Thanks Blue

Hi Blue,

One way:

Sub makesheet()
Dim sh As Worksheet
On Error Resume Next
Set sh = Sheets(Format(Date, "dd-mmm"))
On Error GoTo 0

If Not sh Is Nothing Then
sh.Activate
Else
Sheets("Master").Copy after:=Sheets("Master")
ActiveSheet.Name = Format(Date, "dd-mmm")
End If

End Sub

---
Regards,
Norman



"Blue" wrote in message
k...
I have the following on a button

Sub makesheet()
Sheets("Master").Select
Sheets("Master").Copy after:=Sheets("Master")
ActiveSheet.Name = Format(Now, "dd-mmm")
End Sub

Trouble is if the sheet already exists I get an error message. What I
want
is if the worksheet with today's date already exists make it active. If
it
does not exist create sheet.

Thanks Blue

Thanks for your replies.

Blue