Macro :New Tab for each value in drop down

James01

Board Regular
Joined
Oct 29, 2008
Messages
124
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Possibly

Code:
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

Board Regular
Joined
Oct 29, 2008
Messages
124
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

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,639
Messages
5,626,040
Members
416,155
Latest member
bokboxes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top