Automatically name a new worksheet with a date sequence

New Contributor



I am trying to create a workbook in which new worksheet names are automatically showing the current date and month.

For example, i have a worksheet named May 2019. I would like to insert a new worksheet that is automatically renamed to Jun 2019. When i create one in July, it would automatically be renamed to Jul 2019 and so on...

Is there a way to do this, please?


Thank you.

11 Replies
Honestly, having a worksheet for each month will not make your life simpler as soon as you're asked to do reporting on your file. If you want to show an overview of monthly totals, a single table which includes a date column is a lot simpler.

@Jan Karel Pieterse I am not looking for any reporting afterwards. We are looking at each sheet in the month it occurs. There will be too much data on one sheet to put all the months on one sheet. But thank you for your suggestion. 

Best Response confirmed by Sergei Baklan (MVP)

@AnaPistol This macro checks if there alreay is a worksheet for the current month. If not it creates one. If there is, it selects it:

Sub AddMonthSheet()
    Dim sName As String
    Dim oSh As Worksheet
    sName = Format(Date, "yyyy-mmm")
    On Error Resume Next
    Set oSh = Worksheets(sName)
    If oSh Is Nothing Then
        Set oSh = Worksheets.Add
    End If
    oSh.Name = sName
End Sub

@Jan Karel Pieterse  Thank you! This worked exactly how i needed it to!

Sheer inadvertence, honest mistake, or excusable negligence indubitably caused you to confirm your thanks to @Jan Karel Pieterse as the best response! Please be circumspect next time. I also call the attention of @Eric Starker to perhaps do something to avert a similar occurrence.

@Twifoo This is a very large site with a number of different communities, so our team does not have time to police best responses, which is voluntary on the part of users (please don't tag me on these).


If you have an overall suggestion for the site, feel free to submit it in our Community Ideas space

Thanks for the reminder and advice. I humbly accept my imperfection. If it won’t be too much to ask, perhaps you may make the suggestion on my behalf at the relevant space you cited.

@Twifoo I mentioned it in case you have an overall suggestion for how we handle Best Responses. I don't know what your overall suggestion is, so I can't really do it for you. I'm also a community manager and that idea board is specifically for ideas from the community itself, not from the staff.



Very well said, @Eric Starker. Thanks again for the reminder. I do appreciate it so much.

@Twifoo , @Eric Starker - here I moved the best response on Jan Karel

Nice MOVE, @Sergei Baklan. It reminds me to complete the finishing touches of the formulas for the MOVEs on the Chess Games file I plan to share on the 20th, which is World Chess Day. I love Excel and Chess. That file will epitomize such love.