CODEWITHSUNDEEP
excelvbadatedynamic
Romio Rodriguez
Romio Rodriguez

Reputation: 79

Selecting a sheet with a dynamic name which contains a date

I have been trying to select one of the sheets called "SYS 6.10.2020". However, this sheet changes its date every week. Is there a VBA code that code could select the sheet based on today's date?

Upvotes: 2

Views: 801

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Worksheet Name

The Code

Option Explicit

' If it is the only worksheet that starts with "SYS ", "sys "...
Sub PartialMatch()
        
    Const wsName As String = "SYS "
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        If StrComp(Left(ws.Name, Len(wsName)), wsName, vbTextCompare) = 0 Then
            Exit For
        End If
    Next
    
    If ws Is Nothing Then
        'MsgBox "Worksheet starting with '" & wsName _
             & "' not found.", vbCritical, "Fail"
        Debug.Print "Worksheet starting with '" & wsName & "' not found."
        Exit Sub
    End If
    
    ' Continue with code...
    Debug.Print ws.Name
    
End Sub
        
Sub ExactMatch()
        
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim wsName As String
    wsName = "SYS " & Replace(Format(Date, "d/m/yyyy"), "/", ".")
    
    On Error Resume Next
    Dim ws As Worksheet
    Set ws = wb.Worksheets(wsName)
    On Error GoTo 0
    
    If ws Is Nothing Then
        'MsgBox "Worksheet '" & wsName & "' not found.", vbCritical, "Fail"
        Debug.Print "Worksheet '" & wsName & "' not found."
        Exit Sub
    End If
    
    ' Continue with code...
    Debug.Print ws.Name
    
End Sub

Upvotes: 1

Menelaos
Menelaos

Reputation: 25725

Static String

You can do it by supplying the date (formatted in the appropriate format) to the Sheets function.

Quick Sample:

Sheets("SYS 6.10.2020").Select

Dynamic String

 Sub Task1()

Dim myDate
myDate = Date
   
   MsgBox myDate
   
Dim LValue As String
 LValue = "SYS " & Format(myDate, "dd.mm.yyyy")

MsgBox LValue


 Sheets(LValue).Activate

End Sub

If there is only one sheet added per week, you could use the calendar within vba to find the last date (e.g. last monday) and generate the name from that.

Upvotes: 1

Related Questions