Name worksheet as today's date on open

[gers1978]

I’m looking to write some code so that when the workbook opens it loops through all the worksheet names looking for one named with today’s date. If it finds one, do nothing. If it doesn’t, I want to find the one with the most recent date, take a copy of it, and rename the new copy of the sheet to today’s date.

Any ideas? I'm stuck even knowing where to begin

Also posted here: http://www.ozgrid.com/forum/showthread.php?t=201341&p=778457#post778457

 

[wideboydixon]

What date format are you using for the sheets?

WBD

 

[gers1978]

What date format are you using for the sheets?

WBD

None, at the moment, I just have a "Sheet1". So I'll probably manually name that one with today's date when I have the code ready, then the code will work from there. So I guess it can be whichever format is easiest to work with, probably dd-mm-yy

 

[wideboydixon]

Alt+F11 to open the VBA editor and then double-click "ThisWorkbook" to open up the editor for the workbook. Paste this code:

Private Sub Workbook_Open()


Dim nextWorksheet As Worksheet
Dim todaysDate As String
Dim foundSheet As Boolean
Dim latestDate As Date
Dim checkDate As Date
Dim dateParts() As String


todaysDate = Format(Date, "dd-mm-yy")
latestDate = 0
foundSheet = False


For Each nextWorksheet In Worksheets
    If nextWorksheet.Name = todaysDate Then
        nextWorksheet.Activate
        foundSheet = True
        Exit For
    Else
        dateParts = Split(nextWorksheet.Name, "-")
        If UBound(dateParts) = 2 Then
            If IsNumeric(dateParts(0)) And IsNumeric(dateParts(1)) And IsNumeric(dateParts(2)) Then
                checkDate = DateSerial(CInt(dateParts(2)) + 2000, CInt(dateParts(1)), CInt(dateParts(0)))
                If checkDate > latestDate And checkDate < Date Then latestDate = checkDate
            End If
        End If
    End If
Next


If Not foundSheet And latestDate > 0 Then
    Set nextWorksheet = Worksheets(Format(latestDate, "dd-mm-yy"))
    nextWorksheet.Copy After:=nextWorksheet
    Worksheets(nextWorksheet.Index + 1).Name = todaysDate
    Worksheets(nextWorksheet.Index + 1).Activate
End If


End Sub

WBD

 

[dmt32]

not tested but another way maybe:

Private Sub Workbook_Open()
    Dim a As Long
    Dim sh As Worksheet
    Const dateformat As String = "dd-mm-yyyy"
    
    On Error Resume Next
    Set sh = Worksheets(Format(Date, dateformat))
    If Err.Number = 9 Then
        For Each sh In ThisWorkbook.Worksheets
            If IsDate(sh.Name) Then
                If DateValue(sh.Name) > a Then a = DateValue(sh.Name)
            End If
        Next sh
        Worksheets(Format(a, dateformat)).Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = Format(Date, dateformat)
        Err.Clear
    End If
End Sub

Dave

 

[wideboydixon]

another way maybe:

Private Sub Workbook_Open()
    Dim a As Long
    Dim sh As Worksheet
    Const dateformat As String = "dd-mm-yyyy"
    
    On Error Resume Next
    Set sh = Worksheets(Format(Date, dateformat))
    If Err.Number = 9 Then
        For Each sh In ThisWorkbook.Worksheets
            If IsDate(sh.Name) Then
                If DateValue(sh.Name) > a Then a = DateValue(sh.Name)
            End If
        Next sh
        Worksheets(Format(a, dateformat)).Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = Format(Date, dateformat)
        Err.Clear
    End If
End Sub

Dave

I tend to shy away from implicit conversion of strings to date values since you can't be sure how it will get converted. For example, what does the string "08-02-16" represent? 8th Feb 2016, 16th Feb 2008? 2nd August 2016? Note that IsDate will also return True for anything that could be interpreted as a date so if you happened to have a sheet called "12:00" then this is considered a valid date.

WBD

 

[dmt32]

I tend not to make comments on other contributors posts my Post is directed at the OP & if wants to develop my suggestion further I will be more than happy to assist.

Dave

 

[wideboydixon]

I tend not to make comments on other contributors posts my Post is directed at the OP & if wants to develop my suggestion further I will be more than happy to assist.

Dave

Hi Dave,

No insult intended and sorry that I offended you. I just wanted to outline a potential "gotcha" to the OP. I won't comment on your posts again.

WBD

 

[dmt32]

Hi Dave,

No insult intended and sorry that I offended you. I just wanted to outline a potential "gotcha" to the OP. I won't comment on your posts again.

WBD

No insult or offence taken – all programmers have their own methods of achieving the same result & not all may agree with approach used to get there. The primary purpose of the board is to help the OP & from all contributions, we all can learn from each other.

Whilst nothing wrong in pointing out posting / coding errors from another contributor I personally, would not engage in critiquing another’s offered solution, it’s not what the board is about & certainly not helpful to the OP.

You can answer some of your own questions about date strings by reading up on the DateValue function in VBA helpfile to understand what my suggestion is meant to be doing.

Solution is untested & will require feedback from OP but should in the main, do what they wanted.

Dave