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]

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:
View attachment 113901
The drop down is in O9, the list source is in column H

Yes exactly, but I cannot use the list source in H as the O9 will not be updated for each copied tabs.
My objective is to have a tabs for each value on that O9 with O9 updated with these values. So if the list source has 3 values, I would have 3 tabs:
1 tab renamed Apple and O9 is Apple
2 tab renamed Orange and O9 becomes Orange
3 tab renamed Cherry and O9 becomes Cherry

 

[dmt32]

You stated in your first post

For example, if in A13 i have the following values : Apple, Orange, Cherry

If you have entered the values in the Data Validation list as stated then code should work - Suggest check the list.

Dave

 

[Micron]

Yes exactly, but I cannot use the list source in H as the O9 will not be updated for each copied tabs.

That was only for my example.

If you have managed to put both the validation list source and the validation drop down in the same cell then maybe I get why you're ignoring my suggestions to tell us where the list is. I've shown you twice how to figure that out, albeit once for a forms control, and still you have not provided what I'm asking for. IF the list is not in A13 (or wherever your drop down is) then this part of the code
Split(sh1.Range("A13").Validation.Formula1, ",")
totally ignores what I'm saying since A13 will only have one value. You cannot split one value.

I think I'm out, given that you don't provide the clarification I think is needed. Good luck.

 

[onche414]

That was only for my example.

If you have managed to put both the validation list source and the validation drop down in the same cell then maybe I get why you're ignoring my suggestions to tell us where the list is. I've shown you twice how to figure that out, albeit once for a forms control, and still you have not provided what I'm asking for. IF the list is not in A13 (or wherever your drop down is) then this part of the code
Split(sh1.Range("A13").Validation.Formula1, ",")
totally ignores what I'm saying since A13 will only have one value. You cannot split one value.

I think I'm out, given that you don't provide the clarification I think is needed. Good luck.

I do not see in any of your message where you asked for the position of the list. And I told you I do not have a form controls, only a data validation list in A13 which is based on range of cells from CI1:CI80.
You answer in a quite arrogant manner, chill out.

 

[onche414]

You stated in your first post



If you have entered the values in the Data Validation list as stated then code should work - Suggest check the list.

Dave

Sorry for the confusion, Apple Orange and Cherry are the values but there are in cell CI1 to CI3. There are not listed in a single cell with a separation ","

 

[Micron]

Post 6

To solve your issue you need to figure out where the list source range is.

I do not see in any of your message where you asked for the position of the list.

post 9 - I even gave you a pic on how to figure out where the validation list is for your case, not a control of any kind.

which is based on range of cells from CI1:CI80.

Finally, but too late for me. Putting you on my ignore list now.

 

[onche414]

Post 6



post 9 - I even gave you a pic on how to figure out where the validation list is for your case, not a control of any kind.



Finally, but too late for me. Putting you on my ignore list now.

Why do you feel the need to say that you are putting me in your ignore list ? :D I do not care dude you can get out bye bye mr arrogant.

 

[dmt32]

Sorry for the confusion, Apple Orange and Cherry are the values but there are in cell CI1 to CI3. There are not listed in a single cell with a separation ","

Not to worry partly my fault as I only glanced at your first post & not the whole thread.

If your list refers to a range then see if the update to the suggestion works for you

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)
            ActiveSheet.Name = Left(sValidation(i, 1), 31)
        End If
    Next i
End Sub

Note: your list is very long - check to ensure that this is what you want to do first as code reads all cells in the specified range in to the array.

Dave

 

[onche414]

Not to worry partly my fault as I only glanced at your first post & not the whole thread.

If your list refers to a range then see if the update to the suggestion works for you

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)
            ActiveSheet.Name = Left(sValidation(i, 1), 31)
        End If
    Next i
End Sub

Note: your list is very long - check to ensure that this is what you want to do first as code reads all cells in the specified range in to the array.

Dave

Hi Dave,

Thank you for your answer, it is indeed doing everything I asked for, except the cell A13 is not updating according to the values in column CI.

As an example here, the cell A13 should have the same value than the name of the tab. So I would have the tab new template copied and renamed Apple and cell A13 is Apple, then a tab renamed Orange and the cell A13 is changed to Orange, etc:

 

[dmt32]

As an example here, the cell A13 should have the same value than the name of the tab. So I would have the tab new template copied and renamed Apple and cell A13 is Apple, then a tab renamed Orange and the cell A13 is changed to Orange, etc:

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