Automatically rename a template sheet with today's date.

[MasterBash]

Hello,

So I made a template sheet that I saved to %appdata%\Microsoft\Templates, so I select it when I right click the active sheet name -> insert > select my template.

However, I am looking to do one of those 2 things :

When I click the + to create a new sheet, I would like it to automatically add my template and today's date as the name of the sheet.

or

If I insert my template, automatically change the name of my new sheet with the template to today's date.

How can I do that ? I know nothing with VBA. I appreciate the help.

[Aussiebear]

Welcome to VBAX MasterBash. A quick correction if you don't mind. Templates cannot be saved as a template sheet. I refer to the following from Microsoft


A template is a pre-defined workbook (with one or more worksheets) that can be used to help you create your final workbook. Using templates that contain pre-formatted worksheets can save you a lot of time. Every time you select (File > New) a template is used to create the blank workbook.

Don't save data to a template. Open the template and save it as a new workbook with a xlsm format. With the new workbook, you can implement the following code



Sub NewNamedSheet1()
Dim ws As Worksheet
Dim NameCount As Long
Dim NameBase As String
NameBase = Format(Date, "mm.dd.yyyy")
Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameBase
End Sub

[jdelano]

Another possible option that is available is to create an add-in for Excel, then put it on one of the ribbon menus. When you click it have it insert the sheet and rename it to today's date.

Build an Excel Add-In in Microsoft Excel (exceltip.com) (https://www.exceltip.com/general-topics-in-vba/build-an-excel-add-in-in-microsoft-excel.html)

[MasterBash]

Welcome to VBAX MasterBash. A quick correction if you don't mind. Templates cannot be saved as a template sheet. I refer to the following from Microsoft



Don't save data to a template. Open the template and save it as a new workbook with a xlsm format. With the new workbook, you can implement the following code



Sub NewNamedSheet1()
Dim ws As Worksheet
Dim NameCount As Long
Dim NameBase As String
NameBase = Format(Date, "mm.dd.yyyy")
Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameBase
End Sub


Maybe I was misunderstood, but I do not save data to a template. I create a workbook and when I right click -> Insert to insert a new sheet, then I select the template I created.

Thank you ! It opens a blank sheet with the correct date. Would it be possible to add the template to it too ? Lets say I put the template in the same directory as the workbook, would it be possible to load the template along with the code above ?
In other words, when adding a new sheet, it would change the sheet's name to the current date AND load the template to that sheet ?

[Aussiebear]

Unfortunately from the way your initial post was written, it looked like you had the intention to use the new sheet to contain data.

This concept is based on the template being a hidden sheet in your workbook. if this doesn't work then sing out and we'll have another go



Sub CopyTemplate()
Dim ws as Worksheet
Namebase = Format(Date,"dd,mm,yyyy)
Set ws as Sheet1
ws.visible = xlSheetVisible
s = Sheets(Sheets.Count).Name
ws.Copy After:=Sheets(s)
ActiveSheet.Name = NameBase
ws.Visible = xlSheetHidden
End Sub

[Paul_Hossler]

Maybe I was misunderstood

Maybe

When you say 'template' or 'template sheet' are you talking about an XLTX file (an Excel template workbook that is the basis for a new workbook) or a XLSX file (regular ol' Excel workbook) with a single sheet with formatting, prefilled data, etc. that you want to use as the basis for (say) a new worksheet in the current workbook named 'Sales Figures - 20240601" with headers, logos, formatting, etc.?

[MasterBash]

That is really awesome. It works great !

Would it be possible to add a condition to it ? If a sheet exists with the same date (like... lets say I run the macro twice for today's date), then it throws an error and unhide the template sheet.
So the condition would be to not unhide the template sheet and simply ignore the error (or check if sheet already exists, if it does, do not run the macro).

Thank you so much. :)

[MasterBash]

Maybe

When you say 'template' or 'template sheet' are you talking about an XLTX file (an Excel template workbook that is the basis for a new workbook) or a XLSX file (regular ol' Excel workbook) with a single sheet with formatting, prefilled data, etc. that you want to use as the basis for (say) a new worksheet in the current workbook named 'Sales Figures - 20240601" with headers, logos, formatting, etc.?

XLTX.

However, Aussiebear's solution is working great. I just need to make sure it doesn't throw any error if a user tries to run the macro twice for some reasons. I prefer his solution, because I can just make the macro run when the workbook is opened. It saves us some time.

[Aussiebear]

Hmmm.... maybe this then



Sub test()
Dim ws As Worksheet
Dim Namebase As String
Dim SheetExists As Boolean
Namebase = Format(Date, "dd,mm,yyy")
SheetExists = False
With ThisWorkbook
'Check if the Sheet exists
For Each ws In .Worksheets
If ws.Name = Namebase Then
SheetExists = True
Exit For
End If
Next
If SheetExists = False Then
'If the sheet doesn't exist, then create
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Namebase
End If
End With
End Sub

[MasterBash]

Thank you! It works great. :)
Even better than what I initially asked for !

[Paul_Hossler]

Thank you! It works great. :)
Even better than what I initially asked for !

Glad you got a good solution from AB

Just so I understand (and maybe learn something), it sounds like you're inserting a template (XLTX) into a macro file (XLSM)

I've only ever used excel template XLTX files to create a preformatted workbook with macros, etc.: New, Custom, Pick XLTM and get the desired XLSM workbook as the template.

I usually create a hidden WS to use as a model with formatting, events, etc. and copy that to populate, maybe in WB Open

The resulting XLSM has the macros, etc.

[Aussiebear]

Thats a good point Paul

[MasterBash]

Glad you got a good solution from AB

Just so I understand (and maybe learn something), it sounds like you're inserting a template (XLTX) into a macro file (XLSM)


That is what I did initially.

But Aussibear's solution was even better. Now, everything is done when opening the workbook (new sheet with current date if it doesn't exist, model added to that new sheet). It is just more efficient.
What I initially did worked fine, Right click on a sheet -> Insert and insert the xltx file. I just had to fill the table with data in that sheet and save.

Maybe that is not really the correct way of doing it, I don't know.

Now it is just an hidden sheet in the current workbook.