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

 

[Wookiee]

Hello and welcome to the forum!

I believe the problem is you're attempting to extract multiple values from only one cell.

Is your dropdown list linked to a range of cells perhaps?

Not knowing everything about your sheet, here's my suggestion:

If the dropdown values aren't linked to a range, i would create a range on a sheet (later to be hidden). For example, let's say it's Cells A1:A6 in Sheet HideMe.

Then, I would modify your code thusly:

Sub DupliSheets()

Dim sh1 As Worksheet, c As Range
Dim rngList As Range

Set rngList = Sheets("HideMe").Range("A1:A6")

Set sh1 = Sheets("New Template")

For Each c In rngList  '   sh1.Range("A13")

    sh1.Copy _
      After:=Sheets(Sheets.Count)

    With ActiveSheet

      .Name = c.Value
      .Range("A13") = c.Value

   End With

Next c

End Sub

 

[Micron]

A13 is a range consisting of a single cell, so you're saying for each c (range) in that single cell (range). Don't understand how you can have a drop down list but have all the values in one cell (A13). If your values are one long string (just as you describe it) then you will need to separate them into an array, dictionary, or collection and loop over that.

 

[onche414]

Hello and welcome to the forum!

I believe the problem is you're attempting to extract multiple values from only one cell.

Is your dropdown list linked to a range of cells perhaps?

Not knowing everything about your sheet, here's my suggestion:

If the dropdown values aren't linked to a range, i would create a range on a sheet (later to be hidden). For example, let's say it's Cells A1:A6 in Sheet HideMe.

Then, I would modify your code thusly:

Sub DupliSheets()

Dim sh1 As Worksheet, c As Range
Dim rngList As Range

Set rngList = Sheets("HideMe").Range("A1:A6")

Set sh1 = Sheets("New Template")

For Each c In rngList  '   sh1.Range("A13")

    sh1.Copy _
      After:=Sheets(Sheets.Count)

    With ActiveSheet

      .Name = c.Value
      .Range("A13") = c.Value

   End With

Next c

End Sub

Hello, Thank you for your answer.

Yes it is indeed linked to a range of cell ! Your code is indeed copying the tabs and renaming them but the issue is that the cell A13 (the drop down list) is remaining the same value (the first one of the list) of the list in each tabs.

What I wanted to do is to copy the tabs based on the number of values in cell A13. So if there are 3 values, the code would copy the tab and change the cell A13 to value1 and rename the tab to value 1, then copy the tab again and change the cell A13 to value2 and rename the tab to value 2 etc.


Thanks !

 

[onche414]

A13 is a range consisting of a single cell, so you're saying for each c (range) in that single cell (range). Don't understand how you can have a drop down list but have all the values in one cell (A13). If your values are one long string (just as you describe it) then you will need to separate them into an array, dictionary, or collection and loop over that.

Maybe I am not using the correct term but isn't this a drop down list which can contain multiple values in one cell ?

 

[Micron]

No, that's a control that gets its list values from a range of cells somewhere on some sheet. The list values are not contained in that cell - the list choice goes into that cell. To solve your issue you need to figure out where the list source range is. Right click on it, choose Format Control, click Control tab at look at the Input range info. That is assuming the control is a forms control and not an ActiveX control.

 

[onche414]

No, that's a control that gets its list values from a range of cells somewhere on some sheet. The list values are not contained in that cell - the list choice goes into that cell. To solve your issue you need to figure out where the list source range is. Right click on it, choose Format Control, click Control tab at look at the Input range info. That is assuming the control is a forms control and not an ActiveX control.

Thank you for you answer. But it is not a control. It is a drop down list that I created through data validation list where I selected the range of cells (=values) to be included

 

[dmt32]

Maybe I am not using the correct term but isn't this a drop down list which can contain multiple values in one cell ?

View attachment 113897

You are using data validation

not tested but see if this update to your code does what you want

Sub DupliSheets()
    Dim sh1         As Worksheet
    Dim sValidation As Variant
    Dim i           As Long
   
    Set sh1 = ThisWorkbook.Worksheets("New Template")
   
    sValidation = Split(sh1.Range("A13").Validation.Formula1, ",")
   
    For i = LBound(sValidation) To UBound(sValidation)
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sValidation(i)
    Next i
End Sub

Note: there is no error checking


Dave

 

[Micron]

list where I selected the range of cells

Exactly, and same difference in principle. It still has a list range; the list values are not in the cell where the drop down is:

The drop down is in O9, the list source is in column H

 

[onche414]

You are using data validation

not tested but see if this update to your code does what you want

Sub DupliSheets()
    Dim sh1         As Worksheet
    Dim sValidation As Variant
    Dim i           As Long
  
    Set sh1 = ThisWorkbook.Worksheets("New Template")
  
    sValidation = Split(sh1.Range("A13").Validation.Formula1, ",")
  
    For i = LBound(sValidation) To UBound(sValidation)
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sValidation(i)
    Next i
End Sub

Note: there is no error checking


Dave

It only copies the tab once and give an error message: