View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Jim Thomlinson[_3_] Jim Thomlinson[_3_] is offline
external usenet poster
 
Posts: 983
Default 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