Copying active sheet in same workbook & keep sheetname in edit mode

Question

I want to copy, paste active sheet in excel workbook, and put the sheetname in editing mode (like double clicking on sheetname).

For this I'm currently using following macro with sendkeys. But when I run this macro, it changes the numlock & capslock state.

Public Sub CopySheet()
    ActiveSheet.Copy
    Application.SendKeys "%hor", True
End Sub

Is there any particular code/expression available to put the sheetname in editing mode (like double clicking on sheetname)?

Answer 1

you can use the code below, but pay attention when naming the sheet, the name must be unique, as it acts as an index in the collection sheets. If you try to duplicate the name of a sheet it will result in an error.

Sub copyActiveSheet()
    
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets.Count & " Sheet"

End Sub

Answer 2

I understand I could have specified the sheet name in VBA, but the sheet name will not be in any sequence. The user would put the sheet name as per the requirement. In this macro, the goal is to paste a copy of the active sheet just after the active sheet, and put the sheet name in edit mode, so that the user can enter the sheet name. I'm new to VBA; I will try "InputBox". – tdas777 45 mins ago

In that case ask the user to give you the name of the worksheet in advance and then copy and rename it as shown below.

I have commented the code so I hope you should not have an issue understanding it. But if you do then simply ask.

Also if you wish, you can put the below in a Do...Loop. Well, I leave that to you.

Option Explicit

Sub Sample()
    Dim SheeTName As Variant
    
    '~~> Ask for the name
    SheeTName = InputBox("Enter SheetName")
    
    '~~> Check for user interaction
    If StrPtr(SheeTName) = 0 Then
        MsgBox "You chose to cancel"
    ElseIf Len(SheeTName) = 0 Then
        MsgBox "Nothing Entered"
    Else
        Dim ws As Worksheet
        
        '~~> Check if there is already a sheet with that name
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(SheeTName)
        On Error GoTo 0
        
        If ws Is Nothing Then
             '~~> Copy and rename
            With ThisWorkbook
                ActiveSheet.Copy After:=.Worksheets(.Worksheets.Count)
                .Sheets(Worksheets.Count).Name = SheeTName
            End With
        Else
            '~~> If name exists
            MsgBox "Please choose another name. This name is already taken."
        End If
        
    End If
End Sub