hypelink the list name with the duplicated sheets in marco

Chaturvedi, Santosh 430 Reputation points
2026-06-21T08:57:37.16+00:00

Hello - I have the below macro for duplicating the sheets from the list present in sheet called "Dashboard".

Al the sheets are dulicated sucessfully.

I want that the duplicated sheet also be hyperlinked with the list present in sheet name "Dashboard".

Please update this marcro with this above request that each content of the list must be hyperlinked with the respective sheets name.

The macro is as below

Sub CopyAndRenameSheets()

Dim wList As Worksheet

Dim wTemplate As Worksheet

Dim r As Long

Dim lastRow As Long



' Turn off screen flickering to speed up execution

Application.ScreenUpdating = False



' Change "Dash Board" to the exact name of the sheet holding your list

Set wList = ThisWorkbook.Sheets("Dash Board")

' Change "Template" to the exact name of the sheet you want to copy

Set wTemplate = ThisWorkbook.Sheets("S1")



' Finds the last used row in Column C of your list sheet

lastRow = wList.Cells(wList.Rows.Count, "C").End(xlUp).Row



' Loops down Column C (from row 21 to the end of your list)

For r = 21 To lastRow

    ' Copies the template sheet and places it at the very end

    wTemplate.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    

    ' Renames the newly created sheet based on the list

    On Error Resume Next

    ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = wList.Range("C" & r).Value

    On Error GoTo 0

Next r



Application.ScreenUpdating = True

End Sub

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author
Anonymous
2026-06-21T09:45:56.92+00:00

Hi @Chaturvedi, Santosh,

I took some time to recreate your scenario based on what you described and tested it in my own environment, and it seems to be working as expected.

User's image

You can try using the macro below to achieve what you’re looking for, where each item in the list on the Dashboard sheet is also hyperlinked to its corresponding duplicated sheet:

Sub CopyAndRenameSheets()

    Dim wList As Worksheet
    Dim wTemplate As Worksheet
    Dim wNew As Worksheet          ' Reference to the newly created sheet
    Dim wOld As Worksheet          ' Reference to an existing sheet with the same name (if any)
    Dim r As Long
    Dim lastRow As Long
    Dim newName As String          ' Holds the name taken from the list
 
    ' Turn off screen flickering to speed up execution
    Application.ScreenUpdating = False
    ' Turn off confirmation prompts so deleting old sheets runs silently
    Application.DisplayAlerts = False
 
    ' Change "Dash Board" to the exact name of the sheet holding your list
    Set wList = ThisWorkbook.Sheets("Dash Board")
    ' Change "S1" to the exact name of the sheet you want to copy
    Set wTemplate = ThisWorkbook.Sheets("S1")
 
    ' Finds the last used row in Column C of your list sheet
    lastRow = wList.Cells(wList.Rows.Count, "C").End(xlUp).Row
 
    ' Loops down Column C (from row 21 to the end of your list)
    For r = 21 To lastRow
 
        ' Skip empty cells so no blank sheet/link is created
        If Trim(wList.Range("C" & r).Value) <> "" Then
 
            ' Read the target sheet name from the list cell
            newName = Trim(wList.Range("C" & r).Value)
 
            ' --- NEW: Delete any existing sheet that already has this name ---
        
            Set wOld = Nothing
            On Error Resume Next
            ' Try to grab an existing sheet with the same name
            Set wOld = ThisWorkbook.Sheets(newName)
            On Error GoTo 0
            If Not wOld Is Nothing Then
                ' Never delete the template sheet itself
                If wOld.Name <> wTemplate.Name Then wOld.Delete
            End If
            ' ----------------------------------------------------------------
 
            ' Copies the template sheet and places it at the very end
            wTemplate.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
 
            ' --- NEW: Reference the newly created sheet ---
            Set wNew = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
 
            ' Renames the newly created sheet based on the list
            On Error Resume Next
            wNew.Name = newName
            On Error GoTo 0
 
            ' --- NEW: Capture the ACTUAL sheet name (in case rename was adjusted) ---
            newName = wNew.Name
 
            ' --- NEW: Remove any existing hyperlink in the cell first ---
            ' Avoids duplicate/broken links when the macro is run again
            wList.Range("C" & r).Hyperlinks.Delete
 
            ' --- NEW: Add an internal hyperlink from the list cell to the new sheet (cell A1) ---
            ' The quotes around the sheet name allow names that contain spaces
            wList.Hyperlinks.Add _
                Anchor:=wList.Range("C" & r), _
                Address:="", _
                SubAddress:="'" & newName & "'!A1", _
                TextToDisplay:=newName
 
        End If
 
    Next r
 
    ' Restore alerts and screen updating
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
    ' Notify 
    MsgBox "Done! Sheets were recreated and hyperlinked successfully.", vbInformation

End Sub

If it doesn’t work as expected or you run into any issues, feel free to get back to me anytime. I’d be happy to take a closer look and work through it together with you if needed.

Thank you for your patience and cooperation.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Was this answer helpful?

2 people found this answer helpful.
Answer accepted by question author

AI answer

2026-06-21T08:58:02.2133333+00:00

Use the Hyperlinks.Add method on the Dashboard sheet after each sheet is created and renamed. The hyperlink’s SubAddress should point to the new sheet and a cell (for example A1).

Below is the updated macro. It assumes the list is in column C starting at row 21 (as in the original code) and that each cell in column C should become a hyperlink to the corresponding duplicated sheet.

Sub CopyAndRenameSheets()

    Dim wList As Worksheet
    Dim wTemplate As Worksheet
    Dim r As Long
    Dim lastRow As Long
    Dim wsNew As Worksheet
    Dim sheetName As String

    Application.ScreenUpdating = False

    ' Sheet holding the list
    Set wList = ThisWorkbook.Sheets("Dash Board")

    ' Template sheet to copy
    Set wTemplate = ThisWorkbook.Sheets("S1")

    ' Last used row in Column C
    lastRow = wList.Cells(wList.Rows.Count, "C").End(xlUp).Row

    ' Loop down Column C (from row 21 to the end of the list)
    For r = 21 To lastRow

        sheetName = CStr(wList.Range("C" & r).Value)
        If Len(sheetName) > 0 Then

            ' Copy template sheet to the end
            wTemplate.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

            ' Reference the newly created sheet
            Set wsNew = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

            ' Rename the new sheet
            On Error Resume Next
            wsNew.Name = sheetName
            On Error GoTo 0

            ' Create a hyperlink in the list cell to the new sheet (cell A1)
            ' Remove any existing hyperlink first
            If wList.Range("C" & r).Hyperlinks.Count > 0 Then
                wList.Range("C" & r).Hyperlinks.Delete
            End If

            wList.Hyperlinks.Add _
                Anchor:=wList.Range("C" & r), _
                Address:="", _
                SubAddress:="'" & wsNew.Name & "'!A1", _
                TextToDisplay:=sheetName

        End If

    Next r

    Application.ScreenUpdating = True

End Sub

This code:

  • Duplicates the template sheet for each entry in column C.
  • Renames each new sheet to the corresponding list value.
  • Adds a hyperlink in the Dashboard list cell (column C) that points to cell A1 of the corresponding new sheet.

References:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Oldest
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.