Create Worksheet from Master sheet and hyperlink them in Excel

Question

I have a master worksheet listing all data in rows.
A template to add more details for each unique record
Need to create a worksheet for each entry in column "C" and updating the template with details in Name ( D) & Contact ( E) columns of respective row.
Then Hyperlink the respective sheet with the entry in the rows.

With the help of few posts here i was able to run below code, which creates sheets, but i need help of populating data in Template from Master sheet and also creating a Hyperlink

Option Explicit
Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range

With ThisWorkbook                
    Set wsTEMP = .Sheets("Template")
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible) 
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible 
    
    Set wsMASTER = .Sheets("Master")

    Set shNAMES = wsMASTER.Range("C4:C" & Rows.Count).SpecialCells(xlConstants) 
    
    Application.ScreenUpdating = False
    For Each Nm In shNAMES
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then
           wsTEMP.Copy After:=.Sheets(.Sheets.Count)
           ActiveSheet.Name = CStr(Nm.Text)
        End If
    Next Nm
    
    wsMASTER.Activate 
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden
    Application.ScreenUpdating = True
End With

     MsgBox "All sheets created"
End Sub

Master sheet which is updated often

Sample worksheet

Template

So what have you tried so far? Where did you run into problems? Please include that in your question. — braX
I would say loop through the sheets checking the name against Column D in your master (using either .Find or Application.Match) and if it matches one, then do a .value = .value between the master and looped sheet with the relevant cells (Or do another loop through the relevant cells if you want your script a bit cleaner). — Simon
Thank you Simon, I am not so good at VBA, still a noob. Could you help me put the code? — Chetan

Tim Williams Tim Williams · Accepted Answer · 2020-12-30T20:11:06

Try something like this:

Sub SheetsFromTemplate()
    
    Dim wsMaster As Worksheet, wsTemp As Worksheet, wasVisible As Boolean
    Dim shNames As Range, Nm As Range, wsEntry As Worksheet, entryName
    
    With ThisWorkbook
        Set wsTemp = .Sheets("Template")
        wasVisible = (wsTemp.Visible = xlSheetVisible)
        If Not wasVisible Then wsTemp.Visible = xlSheetVisible
        
        Set wsMaster = .Sheets("Master")
    
        Set shNames = wsMaster.Range("C4:C" & Rows.Count).SpecialCells(xlConstants)
        
        Application.ScreenUpdating = False
        For Each Nm In shNames
            entryName = Nm.Text
            Set wsEntry = Nothing 'EDIT
            On Error Resume Next 'ignore error if no sheet with this name
            Set wsEntry = .Sheets(entryName)
            On Error GoTo 0 'stop ignoring errors
            If wsEntry Is Nothing Then
               wsTemp.Copy After:=.Sheets(.Sheets.Count)
               Set wsEntry = .Sheets(.Sheets.Count) 'get the copy
               wsEntry.Name = CStr(Nm.Text)
            End If
            With wsEntry
                'transfer/update values from Master sheet
                .Range("B2").Value = entryName
                .Range("B3").Value = Nm.Offset(0, 1)
                '...etc
                wsMaster.Hyperlinks.Add Anchor:=Nm, Address:="", _
                    SubAddress:=wsEntry.Range("A1").Address(, , , True), _
                    TextToDisplay:=Nm.Text
            End With
        Next Nm
        
        wsMaster.Activate
        If Not wasVisible Then wsTemp.Visible = xlSheetHidden
        Application.ScreenUpdating = True
    End With
    MsgBox "All sheets created"
End Sub

Create Worksheet from Master sheet and hyperlink them in Excel

1 Answers