Function for sheets

**Peter Niklas** · 05-31-2021, 06:53 AM

Hello,

See attached for an illustrative example of what I want to do.

So I have this excel where I copy in a list of names. The number of names varies every time and is between 1-10 names.

After I copy in the list of names in the Dashboard I have to create a copy of the "Template" sheet for every name and rename that specific sheet with the specific name.

In my excel I have a list of 5 names. Is it possible somehow to automate this process? In other words, after I have copied in the list of names in the dashboard that automatically 5 sheets are made with the same formulas as the "Template" tab that are renamed with the names in the list?

Kind Regards,

Peter

**KOKOSEK** · 05-31-2021, 08:11 AM

Use this VBA code:

Sub CopyAndRename()
Dim cell As Range
Dim lastR As Integer
Application.ScreenUpdating = False
lastR = Sheets("Dashboard").Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Sheets("Dashboard").Range("A2:A" & lastR)
    Sheets("Template").Copy After:=Sheets(Worksheets.Count)
    Sheets("Template (2)").Name = Trim(cell.Value)
Next cell
Sheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub

after pasting/input name in col. A on Dashboard, run this macro.

p.s. there is no check that particular sheet exist or not, so in case if sheet NAME1 exist and you put the same name again it gives error.

**Greg M** · 05-31-2021, 08:19 AM

Hi there,

Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:




Option Explicit


Sub CreateWorksheets()

    Const sDASHBOARD    As String = "Dashboard"
    Const sTEMPLATE     As String = "Template"
    Const sNAMES        As String = "ptrNames"

    Dim wksDashboard    As Worksheet
    Dim wksTemplate     As Worksheet
    Dim rNames          As Range
    Dim wksNew          As Worksheet
    Dim sName           As String
    Dim rCell           As Range
    Dim wks             As Worksheet

    Set wksDashboard = ThisWorkbook.Worksheets(sDASHBOARD)
    Set wksTemplate = ThisWorkbook.Worksheets(sTEMPLATE)

    Set rNames = wksDashboard.Range(sNAMES)

    For Each rCell In rNames

        If rCell.Value <> vbNullString Then

            sName = rCell.Value

            On Error Resume Next
                Set wks = ThisWorkbook.Worksheets(sName)
            On Error GoTo 0

            If wks Is Nothing Then

                  wksTemplate.Copy Before:=wksTemplate

                  Set wksNew = ActiveSheet
                      wksNew.Name = sName

            Else: MsgBox "This workbook already contains a worksheet called """ & sName & _
                          vbLf & vbLf & _
                         "Delete this worksheet before running this routine", vbExclamation

                  Exit Sub

            End If

        End If

    Next rCell

    wksDashboard.Activate

End Sub

The highlighted values may be altered to suit your requirements.

You may enter up to ten names in the green cells on the "Dashboard" worksheet.

Hope this helps - please let me know how you get on.

Regards,

Greg M