Copy a sheet which is a template based on a drop down list and rename each copied tab with each value in the drop down list

[onche414]

Hello,

I am new to VBA and trying to create a code to avoid copying manually.
I have a sheet which is a template called "New Template". In that sheet I have a drop down list in cell A13.
I want to create a code which will copy this sheet based on the values on that drop down list.

For example, if in A13 i have the following values : Apple, Orange, Cherry.
The code would put the values Apple on the cell, copy the sheet, then put the value Orange copy the sheet and then put the value Cherry and copy the sheet again. At the end I would have 3 sheets
Additionally, I would like to rename the tab with the values in the drop down list (so the sheet is renamed with Apple, Orange and Cherry).

This is the code I did for now. But it only copies the first value of the list. it does not go through it

Sub DupliSheets()
Dim sh1 As Worksheet, c As Range

Set sh1 = Sheets("New Template")

For Each c In sh1.Range("A13")
    sh1.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = c.Value
    
Next
End Sub

Thank you for your help

 

[onche414]

Try

Sub DupliSheets()
    Dim sh1         As Worksheet
    Dim sValidation As Variant
    Dim i           As Long
  
    Set sh1 = ThisWorkbook.Worksheets("New Template")
  
    'your data validation list range - adjust as required
    sValidation = sh1.Range("CI1:CI80").Value
  
    For i = 1 To UBound(sValidation, 1)
        If Len(sValidation(i, 1)) > 0 Then
            sh1.Copy After:=Sheets(Sheets.Count)
            With ActiveSheet
                .Name = Left(sValidation(i, 1), 31)
                .Range("A13").Value = .Name
            End With
        End If
    Next i
End Sub

Dave

It worked, thank you very much !!

 

[dmt32]

It worked, thank you very much !!

most welcome - glad we were able to help & appreciate your feedback

Dave