Create a new worksheet problem

[Antono]

Hello everybody.

I have a question concerning the creation of a new worksheet.

What I need to do is the following: I have a sheet called "Report". On worksheet open I need this sheet to be copied after last, the new sheet should be renamed with the date of the day, there should be a check to make sure that there is no other sheet with the same name, and the tab of the new sheet should have a different color.
Is this possible?

Thanks for any help

 

[Chihiro]

Yes. It's possible.

Something like below in Workbook Module.

Private Sub Workbook_Open()
Dim oWs As Worksheet, cWs As Worksheet

Set oWs = ThisWorkbook.Worksheets("Report")
oWs.Copy After:=Worksheets(Worksheets.Count)

On Error GoTo errHandle:
Set cWs = Worksheets(Worksheets.Count)

With cWs
    .Name = Format(Date, "mm-dd-yy")
    .Tab.ColorIndex = Weekday(Date, vbMonday) + 2
End With

errHandle:
If Err.Number = 1004 Then
    Application.DisplayAlerts = False
    cWs.Delete
    Application.DisplayAlerts = True
End If
End Sub

 

[Antono]

hello Chihiro,

thank you so much for your prompt reply. I have copied and pasted your solution into a workbook module. All works fine but 2 things: the name (the worksheet is now named Report(2) and the color (all tabs are black)

 

[Chihiro]

Hmm? You sure you got my code exactly as is?
Error Handling portion will delete any duplicate worksheets (i.e. No Report(1) or Report(2) will be left over).

See attached. You can add more sheets manually by adding "+1" to both "Date", then "+2", so on so forth.

Private Sub Workbook_Open()
Dim oWs As Worksheet, cWs As Worksheet

Set oWs = ThisWorkbook.Worksheets("Report")
oWs.Copy After:=Worksheets(Worksheets.Count)

On Error GoTo errHandle:
Set cWs = Worksheets(Worksheets.Count)

With cWs
    .Name = Format(Date + 1, "mm-dd-yy")
    .Tab.ColorIndex = Weekday(Date +1, vbMonday) + 2
End With

errHandle:
If Err.Number = 1004 Then
    Application.DisplayAlerts = False
    cWs.Delete
    Application.DisplayAlerts = True
End If
End Sub

 

[Antono]

Hello Chihiro,

I noticed a strange thing about my workbook. For some reason I had a hidden sheet with the current date. For this reason I have decided to delete it all and re-make it from scratch. I will apply your solution to it and let you know later on.

Thank you for your help

 

[Antono]

Hello Chihiro,

I have applied your code on a brand new program and it works fine. It does EXACTLY all I asked.
Thank you very very much for your help.