Worksheet Creation Help Needed

[DaveUK]

Please could someone suggest VBA code to do the following:

I have a Workbook Called "SALES".

I want to create a yearly worksheet but obviously only if it doesn't exist.

So if for example it was January 2006 then i want to check in the active workbook, called "SALES", for the existance of a worksheet called "2006". If it doesn't exist i want to create one named "2006", if it does exist then i don't need to create it.

Also how do i then after making the worksheet, how do i then call it to apply some VBA. I need to check for the year and then set this to a string called "CurrentYear". Bear in mind that a worksheet "2005" exists so to access this worksheet i would hope that "CurrentYear" will equal "2005". Whereas if it is January 2006 i hope that "CurrentYear" will equal "2006" and so on and so forth.

I assume it will be similar to this :

With Workbook("SALES).Worksheets(CurrentYear).

' Code here

End with

But i am not sure !!!

Please advise.

 

[CraigM]

Have a go with this code and see if you can adapt it. The Test procedure determines the current year and calls another procedure to add a sheet if it does not exist. You can then use the sCurrentYear variable to refer to the current year's sheet.

Sub Test()
Dim sCurrentYear As String

sCurrentYear = CStr(Year(Now))
AddSheetIfDoesNotExist (sCurrentYear)

ThisWorkbook.Worksheets(sCurrentYear).Activate
End Sub

Sub AddSheetIfDoesNotExist(sSheetName As String)
Dim bolSheetExists As Boolean

bolSheetExists = False
With ThisWorkbook
    For Each sht In .Worksheets
        If sht.Name = sSheetName Then
            bolSheetExists = True
            Exit For
        End If
    Next sht
    
    If Not bolSheetExists Then .Worksheets.Add(After:=.Worksheets(.Worksheets.Count)).Name = sSheetName
End With
End Sub