Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
I have a list of 100 names that I need to make worksheets for each one. Is
there a way to do this automatically? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
Sub namesheetsbottomup()
With Sheets("sheet1")'where list is in col A For i = .Cells(.Rows.Count, "a").End(xlUp).Row To 2 Step -1 Sheets.Add.Name = .Cells(i, "a") Next End With End Sub -- Don Guillett SalesAid Software "Scott@CW" wrote in message ... I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
Scott
With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
Man that ROCKS!!!
OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
Assuming Master sheet is named "Master" and has a list in A1:A100
Sub Copy_Sheet() Dim rCell As Range For Each rCell In Sheets("Master").Range("A1:A100") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("B2").Value = rCell.Value Next rCell End Sub Each new sheet will be a copy of "Master" with a new name and have that sheet name in B2 Gord On Thu, 14 Dec 2006 12:03:01 -0800, Scott@CW wrote: Man that ROCKS!!! OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
This latest Macro isn't working for me, and this is exactly what I need.
Help, anyone? "Gord Dibben" wrote: Assuming Master sheet is named "Master" and has a list in A1:A100 Sub Copy_Sheet() Dim rCell As Range For Each rCell In Sheets("Master").Range("A1:A100") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("B2").Value = rCell.Value Next rCell End Sub Each new sheet will be a copy of "Master" with a new name and have that sheet name in B2 Gord On Thu, 14 Dec 2006 12:03:01 -0800, Scott@CW wrote: Man that ROCKS!!! OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
"isn't working" is pretty much vague doncha think? u wanna be a teensie bit
more specific? Make sure that the sheet you want copied is the active sheet before you start the macro, and that the sheet named "Master" has the list of new sheets you want created. HTH, "e12762r" wrote in message ... This latest Macro isn't working for me, and this is exactly what I need. Help, anyone? "Gord Dibben" wrote: Assuming Master sheet is named "Master" and has a list in A1:A100 Sub Copy_Sheet() Dim rCell As Range For Each rCell In Sheets("Master").Range("A1:A100") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("B2").Value = rCell.Value Next rCell End Sub Each new sheet will be a copy of "Master" with a new name and have that sheet name in B2 Gord On Thu, 14 Dec 2006 12:03:01 -0800, Scott@CW wrote: Man that ROCKS!!! OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
Isn't working? Falls over? Does nothing? Does something but not what you
want? Some help maybe? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "e12762r" wrote in message ... This latest Macro isn't working for me, and this is exactly what I need. Help, anyone? "Gord Dibben" wrote: Assuming Master sheet is named "Master" and has a list in A1:A100 Sub Copy_Sheet() Dim rCell As Range For Each rCell In Sheets("Master").Range("A1:A100") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("B2").Value = rCell.Value Next rCell End Sub Each new sheet will be a copy of "Master" with a new name and have that sheet name in B2 Gord On Thu, 14 Dec 2006 12:03:01 -0800, Scott@CW wrote: Man that ROCKS!!! OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
It doesn't do anything. Here's the Code I used; and yes - the List in the
correct Range. Sub Macro1() Dim rCell As Range For Each rCell In Sheets("Master").Range("A100:A131") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("I1").Value = rCell.Value Next rCell End Sub "Bob Phillips" wrote: Isn't working? Falls over? Does nothing? Does something but not what you want? Some help maybe? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "e12762r" wrote in message ... This latest Macro isn't working for me, and this is exactly what I need. Help, anyone? "Gord Dibben" wrote: Assuming Master sheet is named "Master" and has a list in A1:A100 Sub Copy_Sheet() Dim rCell As Range For Each rCell In Sheets("Master").Range("A1:A100") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("B2").Value = rCell.Value Next rCell End Sub Each new sheet will be a copy of "Master" with a new name and have that sheet name in B2 Gord On Thu, 14 Dec 2006 12:03:01 -0800, Scott@CW wrote: Man that ROCKS!!! OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
George,
Now that I'm reading you're post more carefully; does the Worksheet labeled "Master", and the Worksheet with the List on them have to be 2 seperate Worksheet? I was using 1 Worksheet for both... "George Nicholson" wrote: "isn't working" is pretty much vague doncha think? u wanna be a teensie bit more specific? Make sure that the sheet you want copied is the active sheet before you start the macro, and that the sheet named "Master" has the list of new sheets you want created. HTH, "e12762r" wrote in message ... This latest Macro isn't working for me, and this is exactly what I need. Help, anyone? "Gord Dibben" wrote: Assuming Master sheet is named "Master" and has a list in A1:A100 Sub Copy_Sheet() Dim rCell As Range For Each rCell In Sheets("Master").Range("A1:A100") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("B2").Value = rCell.Value Next rCell End Sub Each new sheet will be a copy of "Master" with a new name and have that sheet name in B2 Gord On Thu, 14 Dec 2006 12:03:01 -0800, Scott@CW wrote: Man that ROCKS!!! OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
No, they can be the same, but I wasn't sure from *your* post whether you
were talking about one sheet "preformatted with a ton of formulas" and a separate sheet with the list of names, so I thought I'd make the distinction clear. As written, the code will work with the list of names in Sheets("Master").Range("A1:A100") *but* it will create copies of whatever the active sheet sheet is when you start the macro. If those are the same sheet, fine. Not sure if this is related to it "not working" or not, its simply a guess. HTH, "e12762r" wrote in message ... George, Now that I'm reading you're post more carefully; does the Worksheet labeled "Master", and the Worksheet with the List on them have to be 2 seperate Worksheet? I was using 1 Worksheet for both... "George Nicholson" wrote: "isn't working" is pretty much vague doncha think? u wanna be a teensie bit more specific? Make sure that the sheet you want copied is the active sheet before you start the macro, and that the sheet named "Master" has the list of new sheets you want created. HTH, "e12762r" wrote in message ... This latest Macro isn't working for me, and this is exactly what I need. Help, anyone? "Gord Dibben" wrote: Assuming Master sheet is named "Master" and has a list in A1:A100 Sub Copy_Sheet() Dim rCell As Range For Each rCell In Sheets("Master").Range("A1:A100") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("B2").Value = rCell.Value Next rCell End Sub Each new sheet will be a copy of "Master" with a new name and have that sheet name in B2 Gord On Thu, 14 Dec 2006 12:03:01 -0800, Scott@CW wrote: Man that ROCKS!!! OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming Worksheet tabs
So it doesn't even create any new worksheets? Where have you stored the
code, in a standard code module or a worksheet code module? How does the macro get invoked? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "e12762r" wrote in message ... It doesn't do anything. Here's the Code I used; and yes - the List in the correct Range. Sub Macro1() Dim rCell As Range For Each rCell In Sheets("Master").Range("A100:A131") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("I1").Value = rCell.Value Next rCell End Sub "Bob Phillips" wrote: Isn't working? Falls over? Does nothing? Does something but not what you want? Some help maybe? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "e12762r" wrote in message ... This latest Macro isn't working for me, and this is exactly what I need. Help, anyone? "Gord Dibben" wrote: Assuming Master sheet is named "Master" and has a list in A1:A100 Sub Copy_Sheet() Dim rCell As Range For Each rCell In Sheets("Master").Range("A1:A100") ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = rCell.Value ActiveSheet.Range("B2").Value = rCell.Value Next rCell End Sub Each new sheet will be a copy of "Master" with a new name and have that sheet name in B2 Gord On Thu, 14 Dec 2006 12:03:01 -0800, Scott@CW wrote: Man that ROCKS!!! OK next step I have one master sheet that is preformatted with a ton of formulas. Can I somehow have it copy the sheet and name each sheet from a list still. If so then I need the tab name in cell B2 for each sheet. Either way this has saved me about two hours of sorting and creation. "Gord Dibben" wrote: Scott With a list of names in A1:A100 on the active sheet, this macro will insert 100 new sheets each with a name from A1:A100. Sub Add_Sheets22() Dim rCell As Range For Each rCell In Range("A1:A100") With Worksheets.Add(After:=Worksheets(Worksheets.Count) ) .Name = rCell.Value End With Next rCell End Sub Gord Dibben MS Excel MVP On Thu, 14 Dec 2006 11:02:01 -0800, Scott@CW wrote: I have a list of 100 names that I need to make worksheets for each one. Is there a way to do this automatically? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling info from multiple worksheet tabs? | Excel Discussion (Misc queries) | |||
excel worksheet tabs to be organized vertically in outline format | Excel Worksheet Functions | |||
Should be able to color code or highlight Excel worksheet tabs. | Excel Worksheet Functions | |||
Indirect formula using Data Validation List of Worksheet Tabs | Excel Worksheet Functions | |||
Hierarchical Worksheet Tabs??? New Excel feature? | Excel Discussion (Misc queries) |