Add sheet, and auto name it.

[cadandcode]

I'm trying to manage daily reports. Im doing repetitive things such copy sheet and rename it.
Sheet names are dates. Like 02/03/23 (dd,mm,yy)
I want to add new sheet by copying last sheet and rename it adding 1 day to date like 02/03/23 to 03/03/23.
I tried to do it by macro but it renames new sheet adding (2) at the end of sheet name. It names 02/03/23 (2) but I need 03/03/23

Here is my macro I tried;

Sub CopySheetDateYesterday()
'
' CopySheetDateYesterday Makro
'

'
    Sheets("02.02.23").Select
    Sheets("02.02.23").Copy After:=Sheets(1)
    Sheets("02.02.23 (2)").Select
    Sheets("02.02.23 (2)").Name = "03.02.23"
    Range("B40").Select
    ActiveCell.FormulaR1C1 = "2/2/2023"
    Range("B41").Select
End Sub

 

[shinigamilight]

Just name the first sheet with whatever date you want and start adding new sheets.
This will be pasted in workbook open module.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
             Dim d As Date
             d = Sheets(Sheets.Count - 1).Name
           Sh.Name = d + 1
            
End Sub

 

[cadandcode]

Just name the first sheet with whatever date you want and start adding new sheets.
This will be pasted in workbook open module.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
             Dim d As Date
             d = Sheets(Sheets.Count - 1).Name
           Sh.Name = d + 1
           
End Sub

Thank you but I would like to copy last sheet because it has the data. Copy last sheet + name it date+1

 

[shinigamilight]

Try this

Sub Copy()
    Dim d As Date
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    d = Sheets(Sheets.Count - 1).Name
    ActiveSheet.Name = d + 1
    
End Sub

 

[cadandcode]

Try this

Sub Copy()
    Dim d As Date
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    d = Sheets(Sheets.Count - 1).Name
    ActiveSheet.Name = d + 1
   
End Sub

Worked! Thank you.

 

[Peter_SSs]

Possibly this?

Sub New_Sheet()
  Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
  With Sheets(Sheets.Count)
    .Name = Format(DateValue(Replace(Left(.Name, 8), ".", "/")) + 1, "dd.mm.yy")
  End With
End Sub

 

[cadandcode]

Possibly this?

Sub New_Sheet()
  Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
  With Sheets(Sheets.Count)
    .Name = Format(DateValue(Replace(Left(.Name, 8), ".", "/")) + 1, "dd.mm.yy")
  End With
End Sub

Now I will try this too.
Umm, have to say that that is what I exactly look like since shinigami's one make it from "02/02/23" to "03/02/2023".
Peter's version is exactly keeping format I use in name. Thank you guys for both.

 

[Peter_SSs]

Worked! Thank you.

Might depend on your local date formatting but hat code did not work for me - error.

 

[cadandcode]

Might depend on your local date formatting but hat code did not work for me - error.

If that code didnt work for you, you might be right about version of excel or local zone differences. I agree.