Consulting

Results 1 to 13 of 13

Thread: Automatically rename a template sheet with today's date.

  1. #1

    Automatically rename a template sheet with today's date.

    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.

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    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
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    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)

  4. #4
    Quote Originally Posted by Aussiebear View Post
    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 ?

  5. #5
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    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
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,743
    Location
    Quote Originally Posted by MasterBash View Post
    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.?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    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.

  8. #8
    Quote Originally Posted by Paul_Hossler View Post
    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.

  9. #9
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    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
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    Thank you! It works great.
    Even better than what I initially asked for !

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,743
    Location
    Quote Originally Posted by MasterBash View Post
    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.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Thats a good point Paul
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  13. #13
    Quote Originally Posted by Paul_Hossler View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •