please help me to refresh the macro to hyperlink the sheet name

Thank you. It works; what about the page and the row from the summary sheet getting hidden? Attached is a screenshot for your reference.

Before I click on the click me button, I hide the sample sheet, and also I hide the Sample row in the summary sheet manually. Once I click on clickme button and add the sheet name "abc1". sheet "abc1" got hidden also the hidden sample sheet name got renamed "Sample (2). Also, I hide Row 6 manually since it is a sample row but if you see on the screen shot even Row 7 is hidden. When I unhide the rows the sample row has moved below to the newly created sheet. Please help.

Or help me to add the macro code in the existing code that the sample sheet and sample row should hide as soon as new sheet is created.

Dear Giri.hb

Try this

Sub CreateNewSheet()
    Dim str As String
    Dim SampleRow As ListRow
    Dim SummaryTable As ListObject
    Dim SummarySheet As Worksheet
    Dim NewSheet As Worksheet
    Dim SampleSheet As Worksheet
    Dim ws As Worksheet
    Dim sheetExists As Boolean
    
    ' Prompt for the new sheet name
    str = InputBox("New Sheet Name", "")
    
    If str = "" Then
        MsgBox "You didn't enter a sheet name.", , ""
        Exit Sub
    End If
    
    ' Check if the sheet already exists
    sheetExists = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = str Then
            sheetExists = True
            Exit For
        End If
    Next ws
    
    If sheetExists Then
        MsgBox "Sheet '" & str & "' already exists.", vbExclamation
        Exit Sub
    End If
    
    With ThisWorkbook
        ' Verify "Sample" sheet exists
        On Error Resume Next
        Set SampleSheet = .Worksheets("Sample")
        On Error GoTo 0
        If SampleSheet Is Nothing Then
            MsgBox "The 'Sample' sheet was not found.", vbExclamation
            Exit Sub
        End If
        
        ' Copy the Sample sheet and rename it
        SampleSheet.Copy after:=.Worksheets(.Worksheets.Count)
        Set NewSheet = .Sheets(SampleSheet.Name & " (2)")
        NewSheet.Name = str
        NewSheet.Visible = xlSheetVisible
        
        ' Move the Sample sheet if needed
        If .Sheets("Sample").Index <> 2 Then .Sheets("Sample").Move after:=.Sheets("Summary")
        
        ' Set reference to Summary sheet and table
        On Error Resume Next
        Set SummarySheet = .Sheets("Summary")
        On Error GoTo 0
        If SummarySheet Is Nothing Then
            MsgBox "The 'Summary' sheet was not found.", vbExclamation
            Exit Sub
        End If
        
        On Error Resume Next
        Set SummaryTable = SummarySheet.ListObjects("Table3") ' Replace "SummaryTable" with your actual table name
        On Error GoTo 0
        If SummaryTable Is Nothing Then
            MsgBox "The table 'SummaryTable' was not found on the 'Summary' sheet.", vbExclamation
            Exit Sub
        End If
        
        ' Add a new row to the table in the Summary sheet
        Set SampleRow = SummaryTable.ListRows.Add
        
        ' Insert hyperlink to the new sheet in the first cell of the new row
        SampleRow.Range(1).Formula = "=HYPERLINK(""#'" & str & "'!A1"",""" & str & """)"
        
        ' Copy format and formulas from the sample row (row under headers)
        SummarySheet.Rows(SummaryTable.ListRows(1).Range.Row).Copy
        SampleRow.Range.PasteSpecial Paste:=xlPasteFormats
        SampleRow.Range.PasteSpecial Paste:=xlPasteFormulas
        Application.CutCopyMode = False
        
        ' Activate the Summary sheet
        SummarySheet.Activate
    End With
End Sub

Try this

Sub CreateNewSheet()
    Dim str As String
    Dim SampleRow As ListRow
    Dim SummaryTable As ListObject
    Dim SummarySheet As Worksheet
    Dim NewSheet As Worksheet
    Dim SampleSheet As Worksheet
    Dim ws As Worksheet
    Dim sheetExists As Boolean
    Dim nextRow As Long
    Dim cell As Range
    
    ' Prompt for the new sheet name
    str = InputBox("Enter New Sheet Name:", "Create New Sheet")
    
    If str = "" Then
        MsgBox "You didn't enter a sheet name.", , "Error"
        Exit Sub
    End If
    
    ' Check if the sheet already exists
    sheetExists = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = str Then
            sheetExists = True
            Exit For
        End If
    Next ws
    
    If sheetExists Then
        MsgBox "Sheet '" & str & "' already exists.", vbExclamation
        Exit Sub
    End If
    
    With ThisWorkbook
        ' Verify "Sample" sheet exists
        On Error Resume Next
        Set SampleSheet = .Worksheets("Sample")
        On Error GoTo 0
        If SampleSheet Is Nothing Then
            MsgBox "The 'Sample' sheet was not found.", vbExclamation
            Exit Sub
        End If
        
        ' Copy the Sample sheet and rename it
        SampleSheet.Copy after:=.Worksheets(.Worksheets.Count)
        Set NewSheet = .Sheets(SampleSheet.Name & " (2)")  ' Newly created sheet is named based on Sample
        NewSheet.Name = str  ' Rename the copied sheet to the user-specified name
        NewSheet.Visible = xlSheetVisible
        
        ' Move the Sample sheet if needed
        If .Sheets("Sample").Index <> 2 Then .Sheets("Sample").Move after:=.Sheets("Summary")
        
        ' Set reference to Summary sheet and table
        Set SummarySheet = .Sheets("Summary")
        Set SummaryTable = SummarySheet.ListObjects("Table3")  ' Ensure "Table3" is the correct table name
        
        ' Find the next available row in column A of the Summary sheet (ignoring "Sample")
        nextRow = SummarySheet.Cells(SummarySheet.Rows.Count, "A").End(xlUp).Row + 1
        
        ' Add the new sheet name in Column A with a hyperlink
        SummarySheet.Cells(nextRow, 1).Formula = "=HYPERLINK(""#'" & str & "'!A1"",""" & str & """)"
        
        ' Remove Data Validation from columns B to W in the new row (clear dropdowns)
        SummarySheet.Range("B" & nextRow & ":W" & nextRow).Validation.Delete
        
        ' Copy formulas from previous row (columns B to W)
        SummarySheet.Range("B" & nextRow - 1 & ":W" & nextRow - 1).Copy
        
        ' Paste the formulas into the new row (columns B to W)
        SummarySheet.Range("B" & nextRow & ":W" & nextRow).PasteSpecial Paste:=xlPasteFormulas
        
        ' Adjust the formulas to point to the new sheet
        For Each cell In SummarySheet.Range("B" & nextRow & ":W" & nextRow)
            ' Update the formula to point to the new sheet
            cell.Formula = Replace(cell.Formula, "'" & SummarySheet.Cells(nextRow - 1, 1).Value & "'", "'" & str & "'")
        Next cell
        
        Application.CutCopyMode = False
        
        ' Activate the Summary sheet
        SummarySheet.Activate
    End With
End Sub

Dear

now i update the vba please check

Sub CreateNewSheet()
    Dim str As String
    Dim SummarySheet As Worksheet
    Dim NewSheet As Worksheet
    Dim SampleSheet As Worksheet
    Dim ws As Worksheet
    Dim sheetExists As Boolean
    Dim nextRow As Long
    Dim cell As Range
    Dim tableRange As Range
    Dim SummaryTable As ListObject

    ' Prompt for the new sheet name
    str = InputBox("Enter New Sheet Name:", "Create New Sheet")
    
    If str = "" Then
        MsgBox "You didn't enter a sheet name.", , "Error"
        Exit Sub
    End If
    
    ' Check if the sheet already exists
    sheetExists = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = str Then
            sheetExists = True
            Exit For
        End If
    Next ws
    
    If sheetExists Then
        MsgBox "Sheet '" & str & "' already exists.", vbExclamation
        Exit Sub
    End If
    
    With ThisWorkbook
        ' Verify "Sample" sheet exists
        On Error Resume Next
        Set SampleSheet = .Worksheets("Sample")
        On Error GoTo 0
        If SampleSheet Is Nothing Then
            MsgBox "The 'Sample' sheet was not found.", vbExclamation
            Exit Sub
        End If
        
        ' Copy the Sample sheet and rename it
        SampleSheet.Copy after:=.Worksheets(.Worksheets.Count)
        Set NewSheet = .Sheets(SampleSheet.Name & " (2)")  ' Newly created sheet is named based on Sample
        NewSheet.Name = str  ' Rename the copied sheet to the user-specified name
        NewSheet.Visible = xlSheetVisible
        
        ' Move the Sample sheet if needed
        If .Sheets("Sample").Index <> 2 Then .Sheets("Sample").Move after:=.Sheets("Summary")
        
        ' Set reference to Summary sheet
        Set SummarySheet = .Sheets("Summary")
        
        ' Find the next available row in the Summary sheet (for new sheet name and formulas)
        nextRow = SummarySheet.Cells(SummarySheet.Rows.Count, "A").End(xlUp).Row + 1
        
        ' Ensure we start from row 5 if the next row is above 5
        If nextRow < 5 Then nextRow = 5
        
        ' Add the new sheet name in the new row (Column A) with hyperlink
        SummarySheet.Cells(nextRow, 1).Formula = "=HYPERLINK(""#'" & str & "'!A1"",""" & str & """)"
        
        ' Copy formulas from the previous row (Row 4) and paste them into the new row
        SummarySheet.Range("B4:W4").Copy
        SummarySheet.Range("B" & nextRow & ":W" & nextRow).PasteSpecial Paste:=xlPasteFormulas
        
        ' Update the formulas to reference the new sheet
        For Each cell In SummarySheet.Range("B" & nextRow & ":W" & nextRow)
            cell.Formula = Replace(cell.Formula, "'" & SummarySheet.Cells(4, 1).Value & "'", "'" & str & "'")
        Next cell
        
        ' Define the range for the table starting from row 5 and including all rows up to the new one
        Set tableRange = SummarySheet.Range("A5:W" & nextRow)
        
        ' Check if there's an existing table
        On Error Resume Next
        Set SummaryTable = SummarySheet.ListObjects(1) ' Assuming there's only one table
        On Error GoTo 0
        
        ' If no table exists, create one
        If SummaryTable Is Nothing Then
            ' Create a new table starting from row 5, including the header row and new row
            Set SummaryTable = SummarySheet.ListObjects.Add(xlSrcRange, tableRange, , xlYes)
            SummaryTable.Name = "SummaryTable"
        Else
            ' If a table exists, resize it to include the new row
            SummaryTable.Resize SummarySheet.Range("A5:W" & nextRow)
        End If
        
        Application.CutCopyMode = False
        
        ' Activate the Summary sheet
        SummarySheet.Activate
    End With
End Sub

Check this also

Sub CreateNewSheet()
    Dim str As String
    Dim SummarySheet As Worksheet
    Dim NewSheet As Worksheet
    Dim SampleSheet As Worksheet
    Dim ws As Worksheet
    Dim sheetExists As Boolean
    Dim nextRow As Long
    Dim cell As Range
    Dim formulaRange As Range
    Dim oldSheetName As String
    
    ' Prompt for the new sheet name
    str = InputBox("Enter New Sheet Name:", "Create New Sheet")
    
    If str = "" Then
        MsgBox "You didn't enter a sheet name.", , "Error"
        Exit Sub
    End If
    
    ' Check if the sheet already exists
    sheetExists = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = str Then
            sheetExists = True
            Exit For
        End If
    Next ws
    
    If sheetExists Then
        MsgBox "Sheet '" & str & "' already exists.", vbExclamation
        Exit Sub
    End If
    
    With ThisWorkbook
        ' Verify "Sample" sheet exists
        On Error Resume Next
        Set SampleSheet = .Worksheets("Sample")
        On Error GoTo 0
        If SampleSheet Is Nothing Then
            MsgBox "The 'Sample' sheet was not found.", vbExclamation
            Exit Sub
        End If
        
        ' Copy the Sample sheet and rename it
        SampleSheet.Copy after:=.Worksheets(.Worksheets.Count)
        Set NewSheet = .Sheets(SampleSheet.Name & " (2)")  ' Newly created sheet is named based on Sample
        NewSheet.Name = str  ' Rename the copied sheet to the user-specified name
        NewSheet.Visible = xlSheetVisible
        
        ' Set reference to Summary sheet
        Set SummarySheet = .Sheets("Summary")
        
        ' Determine the next available row in the Summary sheet starting from row 5
        nextRow = SummarySheet.Cells(SummarySheet.Rows.Count, "A").End(xlUp).Row + 1
        If nextRow < 5 Then nextRow = 5  ' Ensure we start from row 5
        
        ' Add the new sheet name in the new row (Column A) with hyperlink
        SummarySheet.Cells(nextRow, 1).formula = "=HYPERLINK(""#'" & str & "'!A1"",""" & str & """)"
        
        ' Copy formulas from row 6 to the new row
        Set formulaRange = SummarySheet.Range("B6:W6")
        formulaRange.Copy
        
        ' Paste formulas in the new row
        SummarySheet.Range("B" & nextRow & ":W" & nextRow).PasteSpecial Paste:=xlPasteFormulas
        
        ' Replace old sheet reference with the new sheet name in the formulas
        oldSheetName = SummarySheet.Cells(6, 1).Value  ' Get the old sheet name from row 6
        
        For Each cell In SummarySheet.Range("B" & nextRow & ":W" & nextRow)
            ' Update the sheet reference in the formulas
            cell.formula = Replace(cell.formula, "'" & oldSheetName & "'", "'" & str & "'")
        Next cell
        
        ' Cleanup
        Application.CutCopyMode = False
        SummarySheet.Activate  ' Optionally activate the Summary sheet
    End With
End Sub

just replace the code and run the code