Copy existing sheet and rename from a list on a different sheet

**me55er** · 07-02-2017, 11:14 AM

Hi i hope you can help i am very new to this and have a understanding of the very basics.

i am creating a database for my clients and on the page named "clients" there is a list of clients that i keep adding to and when i add one i would like it to automatically copy a template sheet named "assesment" and rename it after that client.

I understand how to open the coding window but ive tried lots of diffrent codes but im getting no reactions please help.

**xladept** · 07-02-2017, 03:54 PM

Where to put the code depends on the rest of the code but something like

Dim wt as worksheet:Set wt=Sheets("Template Sheet Name")

. 
.
.
wt.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name="Client's Name"
.
.
.

**Logit** · 07-02-2017, 05:18 PM

.
Paste this macro into a Routine Module.


Sub HyperLnksCreate()
Dim wsRecipes As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
Dim i As Long
Dim wsIndex
On Error Resume Next

With ThisWorkbook                                               'keep focus in this workbook
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)              'check if it's hidden or not
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      'make it visible
    
    Set wsIndex = .Sheets("Index")                            'sheet with names
                                                                'range to find names to be checked
    Set shNAMES = wsIndex.Range("A2:A" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas
    
    Application.ScreenUpdating = False                              'speed up macro
    For Each Nm In shNAMES                                          'check one name at a time
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A2)") Then   'if sheet does not exist...
            wsTEMP.Copy After:=.Sheets(.Sheets.Count)               '...create it from template
            'ActiveSheet.Range("A1").Value = (Nm.Text)
            ActiveSheet.Name = CStr(Nm.Text)                        '...rename it
         End If
         
    With Sheets("Index")                                                         'create hyperlinks in list
        For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
        .Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", _
        SubAddress:="'" & .Range("A" & i).Value '& "'!A2" ', TextToDisplay:=.Range("A" & i).Value
        Next i
    End With

    Next Nm
    
    wsIndex.Activate                                           'return to the master sheet
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       'hide the template if necessary
    Application.ScreenUpdating = True                           'update screen one time at the end

End With

MsgBox "All sheets created"
End Sub

In your workbook, name the Sheet1 as TEMPLATE. Name Sheet2 as INDEX. Arrange the TEMPLATE sheet as a complete, blank form.

On INDEX, A1 ... enter SALESMAN NAMES.

Place a command button next to that in B1, and connect the button to the above macro.

Beginning in A2, type the name of a salesman. In A3 repeat, A4 repeat, etc. At any time you can add a new name to the bottom of the list.

The macro creates only new sheets - does not overwrite existing sheets.