Display Worksheet If It Exists

[Blue]

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

 

[Guest]

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...

 

[Norman Jones]

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

 

[Blue]

Thanks for your replies.

Blue