CODEWITHSUNDEEP
excelvba
Usman.haider Bhatti
Usman.haider Bhatti

Reputation: 5

Add Sheet Name as Date and Increment date every run

I use an Excel macro to add sheet name as date. It works.

I want to make it increment on a daily basis.

For example today on 28-09-2018 I click on the button to add a sheet. The next day when I click the button it should add a sheet with the name of 29-09-2018 without getting value from the cell reference.

Sub datesheets()
Dim i As Integer
i = 1

For Each w In Worksheets
    If w.Name = "26.09.2018" Then
        found = True
        Workbook.Sheets.Add(, ActiveSheet).Name = "26.09.2018" + i
    End If
Next
If found = False Then
        Worksheets.Add(, ActiveSheet).Name = "26.09.2018"
End If

End Sub

Upvotes: 0

Views: 2521

Answers (1)

Marcucciboy2
Marcucciboy2

Reputation: 3261

I changed the way that the date is listed - now, the date is based on todays’s date which you get from the Now() function. Using DateAdd() I was able to tell it to add a new sheet with tomorrow’s date.

I also changed the first Worksheets.Add(...) call to place the new day’s sheet immediately after the previous day’s, because that seemed like what you intended to happen. 

Sub datesheets()

    Dim found As Boolean

    Dim w As Worksheet
    For Each w In Worksheets
        If w.Name = Format(Now(), "dd.mm.yyyy") Then
            found = True

            Worksheets.Add(, w).Name = Format(DateAdd("d", 1, Now()), "dd.mm.yyyy")
        End If
    Next w

    If found = False Then
        Worksheets.Add(, ActiveSheet).Name = Format(Now(), "dd.mm.yyyy")
    End If

End Sub

Upvotes: 1

Related Questions