Create worksheet if one does not exist

[hmaveal]

I have a job stock spreadsheet that has all active jobs at a service center on it.
Once the job has been loaded on a truck it is marked "complete" and I move it to the current year "completed" worksheet.

How can I automate this to create a "2012","2013", etc. worksheet if it
does not exist ? I currently have a 2011 worksheet. The new worksheet would be named relavent to a cell that has the "CURRENT" year as a formula. So, on Jan 1,2012...if a job is loaded...the macro would see that there is not a 2012 worksheet ..create one..and continue with moving that job from the active aheet to the completed 2012 sheet.

I have to keep the 2011 worksheet, so just overwriting isnt feasible. I could make a copy of the 2011 and delete all the information but keep the formatting. The automatic named of the sheet as the current year is where I'm having a problem...if one doesn't already exist.

Hope that makes sense to someone .... lol

 

[tweedle]

Would this not be a one-time/year event?
Doesn't seem like a macro is wholistically necessary.

 

[MrKowz]

Try the following. Place this code in "ThisWorkbook" in the VBAProject window:

Public Sub Workbook_Open()
If Not WorksheetExists(Year(Now)) Then
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Year(Now)
End If
End Sub

And then place this code in a module within the same VBAProject.

Function WorksheetExists(WSName As Variant) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function