Found a VBA code to rename sheet name as the current date, but now, trying to make this new sheet as the active worksheet.

[boninm]

Hi everyone

I found a VBA code to rename sheet name as the current date, but now, trying to make this new sheet as the active worksheet.

Everything works well except for making the newly or existing worksheet the active worksheet.
I have tried 2 commands (those rem out and in red), and they don't work.

If you run the code the 1st time, it will create a new worksheet with the current date

If you run it again, it will find it, and tell you. Here, i would like to make that new worksheet the active worksheet.
And this is where I'm stuck.

The uploaded image has run the 1st time and 2nd time, and this is the message box info it gives.
It tells me that it exist, but now I want to that sheet to be the active worksheet

 

[mumps]

It is hard to work with a picture. Use code tags to post your code. You can do so by pasting the code directly in your post, highlighting it and then clicking the "VBA" icon in the menu.

 

[boninm]

In the VBA/Module section, I have this code
I hope this helps

Sub testSheet()
    Dim strName As String
    Dim wk As Worksheet
   
    strName = Format(Date, "YYYY-MM-DD")
    On Error Resume Next
    Set wk = Worksheets(strName)
    On Error GoTo 0
    If wk Is Nothing Then
        Sheets.Add After:=ActiveSheet
        szToday = Format(Date, "YYYY-MM-DD")
        ActiveSheet.Name = szToday
       
    Else: MsgBox "*** Sheet already exists ***"
        MsgBox "Date: " & Format(Date, "YYYY-MM-DD")
        szToday = Format(Date, "YYYY-MM-DD")
        'ActiveSheet.Name = szToday
        'Worksheets(&szToday).Activate
        Exit Sub
    End If
   
End Sub

 

[dmt32]

Hi
welcome to forum

see if this update to your code does what you want

Sub RenameSheet()
    Dim strName     As String
    
    strName = Format(Date, "YYYY-MM-DD")
    
    If Not Evaluate("ISREF('" & strName & "'!A1)") Then
        
        Worksheets.Add(After:=ActiveSheet).Name = strName
        
    Else
        
        MsgBox strName & Chr(10) & "Sheet Already Exists", 48, "Sheet Exists"
        
        Worksheets(strName).Activate
        
    End If
    
End Sub

Dave

 

[boninm]

Yes, it works.
I'm going to have to study what you did.
Again Thank you

 

[dmt32]

Yes, it works.

Glad suggestion resolved your issue

I'm going to have to study what you did.

Nothing too clever or original - I just used a commonly used Evaluate call to test if sheet name exists in workbook and if so, activate it otherwise, add new sheet & name it.

Glad we were able to help & appreciate your feedback

Dave