Display Worksheet If It Exists

  • Thread starter Thread starter Blue
  • Start date Start date
B

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