Macro :New Tab for each value in drop down

[James01]

Hi

I have a drop down which is based on a range called "employees". As I change the values in the drop time, the calcualtions in the spreadsheet automatically change in response to the new value in the drop down.

I use a macro as below, which automatically changes the values in the dropdown and for each value takes a print out.

Sub Printall()
Dim c As Range
For Each c In Range("employees")
Range("C5").Value = c.Value
ActiveSheet.PrintOut
Next c
End Sub

Now instead of taking a print out what I would like to do is for excel to make different tabs for each value in the drop down. So for example if there are 3 values in the range "employees" then 3 new tabs are created for each employee.

Does that make sense? Hopefully someone can help in changing the code.

Thanks

 

[VoG]

Possibly

Sub AddSheets()
Dim c As Range
For Each c In Range("employees")
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c.Value
Next c
End Sub

 

[James01]

HI

Thanks for the reply.

In the spreadsheet there are cells which change in response to the value in the drop down.

Although your code adds new tabs for each value in the range and also names these tabs correctly, but these new tabs are blank. Meaning no cells / formulas are copied to these new tabs.

I also want the cells / formulas to be copied in the new tabs.

I found a macro which does this but there is some error here which I cannot seem to fix

Sub MakeSheet()
Dim sh As Worksheet, c As Range
Set sh = Activesheet
For Each c In Range("employees")
 sh.Range("C5").Value = c.Value
 sh.Copy After:=Worksheets(Worksheets.Count)
 ActiveSheet.Name = ActiveSheet.Range("C5").Value
 sh.Activate
Next c
End Sub

 

[VoG]

Try

Sub CopySheets()
Dim c As Range
For Each c In Range("employees")
    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = c.Value
Next c
End Sub