How do i select a sheet by using a date variable?

Question

I've been trying to select and delete a sheet by using this variable szTodayDate but i keep getting the run-time error 9.

Option Explicit
Dim szTodayDate As String

Public Sub AddSheetsTodayDate()

    szTodayDate = Format(Date, "dd-mmm-yy")

    On Error GoTo MakeSheet
    Sheets(szTodayDate).Activate
    Exit Sub
MakeSheet:
    Sheets.Add , Worksheets(Worksheets.Count)
    ActiveSheet.Name = szTodayDate

End Sub

Public Sub RecordProgress()
    Worksheets(szTodayDate).Select  
End Sub

On debug, what is the value of szTodayDate and what's the Sheet's name? — AAA, Jun 29 '19 at 17:19
It shows a blank value. Just "". The sheet's supposed to be named the current date in the "dd-mm-yy" format. The first sub procedure works and creates a sheet of that name. But when i try to select the worksheet using the variable i've defined "worksheets(sztodaydate).select" runs into an error — Luke, Jun 29 '19 at 17:29
Perhaps, there's no a sheet with such name. Are you confident that `szTodayDate` holds correct name? — JohnyL, Jun 29 '19 at 18:12

AAA · Accepted Answer · 2019-06-29T18:06:03.680

0

The issue is with your declaration of szTodayDate. Change it to:
Public szTodayDate As String

edited Jun 29 '19 at 18:06

answered Jun 29 '19 at 17:54

AAA

3,520
1
15
31

That worked! Strange, I thought declaring variables as public under option explicit made them module level variables and I was working within a module. – Luke Jun 30 '19 at 05:35

Mikku · Answer 2 · 2019-06-30T09:28:07.003

This will work for you:

I have used Evaluate as mentioned by Rory in this Answer to check if the sheet Exists.

Option Explicit
Public szTodayDate As String

Public Sub AddSheetsTodayDate()

    Dim WorksheetExists As Boolean
    Dim ws As Worksheet

        szTodayDate = format(Now, "dd-mmm-yy")
        WorksheetExists = Evaluate("ISREF('" & szTodayDate & "'!A1)")

    If Not WorksheetExists Then

        Sheets.Add , Worksheets(Worksheets.Count)
        ActiveSheet.Name = szTodayDate

    Else: Sheets(szTodayDate).Activate

    End If

End Sub

Public Sub RecordProgress()
    Worksheets(szTodayDate).Select  
End Sub

How do i select a sheet by using a date variable?

2 Answers2