Display Worksheet If It Exists
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
|