Naming worksheets with vba

[MrByte]

In the following code I am checking the workbook to see if the worksheet is there already. The worksheet it is looking for would be the year. i.e. This year is already in the workbook so as to not repeat the year I am adding a new one for 2012. But if the year 2011 is not in there (hypothetically) then I would add 2011. I have the confirmation question in there just as a safe measure, but would rather not have it there. I guess I am about how to write this.

Any suggestions?

Sub CheckYear()
Dim n As Integer
Dim a As Integer
Dim wsn() As String
Dim ny As String

ny = Str(Year(Now()))
'check to see if a worksheet exsists for the new year in the workbook
n = ActiveWorkbook.Worksheets.Count
ReDim wsn(1 To n)
For a = 1 To n
    wsn(a) = ActiveWorkbook.Sheets(a).Name
    If wsn(a) = ny Then Exit For
Next a

ans = MsgBox(("Add this year " & Str(Year(Now)) + 1) & "?", vbYesNoCancel, "Year")
Select Case ans
    Case vbCancel
    Exit Sub
    Case vbNo
    ny = InputBox("What year do you want to add?", "Year", Str(Year(Now()) + 1))
    Worksheets.Add(after:=Worksheets("Main")).Name = Str(ny)
    Exit Sub
    Case vbYes
    Worksheets.Add(after:=Worksheets("Main")).Name = Str(Year(Now()) + 1)
End Select
End Sub

 

[drsarao]

You need to save the "existence" of the sheet within the loop.

Try this:

Sub CheckYear()
Dim n As Integer
Dim a As Integer
Dim wsn() As String
Dim ny As String
Dim nny As String

ny = Str(Year(Now()))
nny = Str(Year(Now()) + 1)
'check to see if a worksheet exsists for the new and new+1 year in the workbook
n = ActiveWorkbook.Worksheets.Count
ReDim wsn(1 To n)
nyExists = False
nnyExists = False
For a = 1 To n
    wsn(a) = ActiveWorkbook.Sheets(a).Name
    If wsn(a) = ny Then nyExists = True
    If wsn(a) = nny Then nnyExists = True
Next a
If nyExists Then
    If nnyExists Then
        MsgBox ny & " & " & nny & " already exist."
    Else
        Worksheets.Add(after:=Worksheets("Main")).Name = nny
    End If
Else
    Worksheets.Add(after:=Worksheets("Main")).Name = ny
End If
End Sub