Excel VBA to Add New Sheet then Change Name to Current Date and Tab Color

[astrid22]

Hi, I am relatively new to Excel VBA and I am trying to create a personal template for my work. I already have the below code to insert a new sheet. But I don't know how to change the tab color based on a fixed cell value.
Sub GenerateConf()
On Error GoTo MyError
Sheets.Add
ActiveSheet.Name = WorksheetFunction.Text(Now(), "mm.dd.yyyy" + " OA ")
Exit Sub
MyError:
MsgBox "There is already a sheet called that."
End Sub

Cell J22 contains the letters OA. I want to be able to insert a new sheet, rename it to the current date + the letters OA + the count of sheets with OA as part of its name on the same day (like (date today) OA 1; (date today) OA 2; (date today) OA 3, etc) and then change the tab color to light green.

Any ideas?

 

[Vbagreenhorn1]

Sub GenerateConf()
 On Error GoTo MyError
 Sheets.Add
 With ActiveSheet
    .Name = WorksheetFunction.Text(Now(), "mm.dd.yyyy" + " OA ")
    .Tab.ColorIndex = 35
 End With
 Exit Sub
MyError:
 MsgBox "There is already a sheet called that."
 End Sub

 

[Peter_SSs]

Cell J22 contains the letters OA.

Cell J22 where? All we know about your workbook is from your code which is adding a brand new worksheet which won't have anything in J22.

 

[astrid22]

Hi, thank you! Works great! But how do I prevent it from still inserting a new sheet if the sheet name already exists?

 

[astrid22]

Cell J22 where? All we know about your workbook is from your code which is adding a brand new worksheet which won't have anything in J22.

Hi, sorry. Vbagreenhorn1 has already solved the tab color part. I only set J22 cell to have the letters OA as I was thinking about using it as a condition to change the color. Like I mentioned I am relatively new to Excel VBA so I am still trying to find my way around it

 

[Peter_SSs]

Hi, thank you! Works great! But how do I prevent it from still inserting a new sheet if the sheet name already exists?

I don't understand that part. You said ..

I want to be able to insert a new sheet, rename it to the current date + the letters OA + the count of sheets with OA as part of its name on the same day (like (date today) OA 1; (date today) OA 2; (date today) OA 3, etc)

If we do the blue part, as my code below does, then there will never be an attempt to name a sheet the same as one already in the workbook.

Sub GenerateConf_v2()
  Dim ws As Worksheet
  Dim NameCount As Long
  Dim NameBase As String
  
  NameBase = Format(Date, "mm.dd.yyyy OA ")
  For Each ws In Worksheets
    If ws.Name Like NameBase & "#*" Then NameCount = NameCount + 1
  Next ws
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameBase & NameCount + 1
  Sheets(Sheets.Count).Tab.ColorIndex = 35
End Sub

 

[astrid22]

I don't understand that part. You said ..

If we do the blue part, as my code below does, then there will never be an attempt to name a sheet the same as one already in the workbook.

Sub GenerateConf_v2()
  Dim ws As Worksheet
  Dim NameCount As Long
  Dim NameBase As String
  
  NameBase = Format(Date, "mm.dd.yyyy OA ")
  For Each ws In Worksheets
    If ws.Name Like NameBase & "#*" Then NameCount = NameCount + 1
  Next ws
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameBase & NameCount + 1
  Sheets(Sheets.Count).Tab.ColorIndex = 35
End Sub

Hi Peter, this is exactly what I am trying to do! Thank you so much! Saved me a lot of time!

 

[Peter_SSs]

Hi Peter, this is exactly what I am trying to do! Thank you so much! Saved me a lot of time!

You are very welcome. Thanks for letting us know.

 

[Ingolf]

@Peter_SSs,

How would your code change as every day the tab has another color?
Obviously the tabs created on the same day have the same color.
Thank you.

 

[Vbagreenhorn1]

Sheets(Sheets.Count).Tab.ColorIndex = Day(Date) + 2