How to make button to create a new sheet named like the previous sheet date put plus one day

Question

0.00/5 (No votes)

See more:

I am making a new project for clinic reservation in excel and in this project. I require a button to click to create a new sheet with a new date, which is one day more than the date of the previous. I found a code in the internet which create a new sheet with my template but it create the sheet with today date, but what I need is to create sheet with one day more then the last sheet created or the active sheet. Can any one please help?

the code i found is :

What I have tried:

VB

Sub Insert_Sheet_Template()
    Dim sh As Worksheet
    Dim shName As String

    'name of the sheet template
    shName = "template.xltm"

    'Insert sheet template
    With ThisWorkbook
        Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
                            after:=.Sheets(.Sheets.Count))
    End With

    'Give the sheet a name, today's date in this example
    On Error Resume Next
    sh.Name = Format(Date, "yyyy-mmm-dd")
    If Err.Number > 0 Then
        MsgBox "Change the name of Sheet : " & sh.Name & " manually"
        Err.Clear
    End If
    On Error GoTo 0
End Sub

Posted 25-Jul-18 0:04am

Member 13924946

Updated 25-Jul-18 4:13am

CHill60

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-07-25T04:12:00

Try this function

VB

Private Function FindNextSheetDate() As String
'Steps through the sheets in ThisWorkbook and determines which (if any) are a date
'determines the latest of those dates and adds 1 day 
'returns that date as a string in yyyy-mmm-dd format
'If no sheets are found, returns today's date
'Error handling is assumed to be handled by the calling function

    Dim dt As Date
    Dim maxdt As Date
    maxdt = CDate("1900-Jan-01")
    
    Dim sht As Variant
    For Each sht In ThisWorkbook.Sheets
        If IsDate(sht.Name) Then
            dt = CDate(sht.Name)
            If dt > maxdt Then
                maxdt = dt
            End If
        End If
    Next

    If maxdt = CDate("1900-Jan-01") Then
        'Set maxdt to yesterday as there is a DateAdd of one day in the return value
        maxdt = DateAdd("d", -1, Date)
    End If

    FindNextSheetDate = Format$(DateAdd("d", 1, maxdt), "yyyy-mmm-dd")
    
End Function

Used like

VB

sh.Name = FindNextSheetDate()