need help to refresh the macro

[Giri.hb]

I have a macro code where it will copy the sample sheet with the new name, and the new sheet name will get added to the summary page with the hyperlink.

I am facing an issue that the new sheet name should get added to the Table in the summary sheet, but it is added outside the table. Header and the sample row are in the Table.

Please check and help me

>>> use code - tags <<<

Sub CreateNewSheet()
    Dim str As String
    Dim SampleRow As Long
   
    str = InputBox("New Sheet Name", "")
    If Not str = "" Then
        With ThisWorkbook
            On Error Resume Next
               n = Sheets(str).Name
            On Error GoTo 0
            If Not IsEmpty(n) Then
                MsgBox "Sheet '" & str & "' already exists.", vbExclamation
                Exit Sub
            End If
            .Worksheets("Sample").Copy after:=.Worksheets(.Worksheets.Count)
            With .Sheets("Sample (2)")
                .Name = str
                .Visible = xlSheetVisible
            End With
                      
            If Sheets("Sample").Index <> 2 Then Sheets("Sample").Move after:=.Sheets("Summary")
           
            With .Sheets("Summary")
                On Error Resume Next
                SampleRow = 0
                SampleRow = .Range("A:A").Find(what:="Sample", lookat:=xlWhole).Row
                On Error GoTo 0
                If SampleRow > 0 Then .Rows(SampleRow).EntireRow.Hidden = False
                .Rows(.Cells(.Rows.Count, 1).End(xlUp).Row + 1).FillDown
                .Cells(.Rows.Count, 1).End(xlUp).Formula = "=HYPERLINK(""#'" & str & "'!A1"",""" & str & """)"
                If SampleRow > 0 Then .Rows(SampleRow).EntireRow.Hidden = True
            End With
            .Sheets("Summary").Activate
        End With
    Else
        MsgBox "You didn't enter a sheet name.", , ""
    End If
End Sub

 

[Debaser]

Cross-post: https://www.excelforum.com/excel-pr...sh-the-macro-to-hyperlink-the-sheet-name.html

 

[vletm]

Giri.hb
As You have read from Forum Rules:

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

It's more possible to get replies by following rules
Please reread from:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org