VBA check sheet name in case and create a new sheet

[S_Tzavaras]

Hello Guys,

i have the following code which works fine however if the sheet already exists an error occurs, i could turn off errors but i am hoping some code can be inserted like an if statement if the sheet exists. i have surfed multiple posts and having trouble getting it to work.

CurrentMonth = DatePart("m", strDate)


'nowMonth = Month(Now)
'nowYear = Year(Now)


Select Case CurrentMonth
Case 1
nowMonth = Month(Now)
nowYear = Year(Now)
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveWorkbook.Sheets(Worksheets.Count).Name = "January" & " " & nowYear
Case 2.......

Case12....

End Select

Hope to get some hints of how this can be coded.

Thanks in advance

Scott

 

[steve the fish]

Try this:

Function SheetExists(sh As String) As Boolean

On Error Resume Next
Set mySheet = Sheets(sh)

SheetExists = Not mySheet Is Nothing

End Function

Sub addsheet()

Dim myDate As String

myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
    MsgBox "sheet does exist"
Else
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = myDate
End If

 

[S_Tzavaras]

Thank you very much steve the fish, you saved me!

The whole code ended up like this:

Function SheetExists(sh As String) As Boolean
Dim mySheet As Variant


On Error Resume Next
Set mySheet = Sheets(sh)


SheetExists = Not mySheet Is Nothing


End Function


Private Sub UserForm_Initialize()


Dim myDate As String
Dim strDate As String
Dim CurrentMonth, nowMonth, nowYear As Integer


Range("A1").Select
Do While ActiveCell.Value <> Empty
ActiveCell.Offset(1, 0).Select
Loop


ActiveCell.Range(Cells(1, 1), Cells(1, 10)).Select
Selection.Merge
With Selection.Font
Font.Name = "Times New Roman"
Font.Size = 14
Font.Bold = True
End With
ActiveCell.HorizontalAlignment = xlCenter


RETRY:
strDate = InputBox("Please enter the run sheet date in this format dd/mm/yyyy", "User date", Format(Now(), "dd/mm/yyyy"))
If IsDate(strDate) Then
strDate = Format(CDate(strDate), "dd/mm/yyyy")
RunSheetDate.Value = strDate
ActiveCell.Value = strDate
ActiveCell.NumberFormat = "dd/mm/yyyy;@"
Else
MsgBox "You have entered the wrong date format, Please enter the date this format dd/mm/yyyy"
GoTo RETRY
End If



CurrentMonth = DatePart("m", strDate)


'nowMonth = Month(Now)
'nowYear = Year(Now)


Select Case CurrentMonth
Case 1
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "January" & " " & nowYear
End If
Case 2
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "February" & " " & nowYear
End If
Case 3
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "March" & " " & nowYear
End If
Case 4
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "April" & " " & nowYear
End If
Case 5
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "May" & " " & nowYear
End If
Case 6
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "June" & " " & nowYear
End If
Case 7
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "July" & " " & nowYear
End If
Case 8
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "August" & " " & nowYear
End If
Case 9
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "September" & " " & nowYear
End If
Case 10
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "October" & " " & nowYear
End If
Case 11
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "November" & " " & nowYear
End If
Case 12
myDate = Format(Now, "mmmm yyyy")
If SheetExists(myDate) Then
ActiveSheet.Select
Else
nowMonth = Month(Now)
nowYear = Year(Now)
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "December" & " " & nowYear
End If
End Select
End Sub