Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Names using Macros
Hi I have 2 Columns in Worksheet....
A B 201 NEB 202 NEB 203 NEB 204 NWB 205 NWB 209 NWB Using formula or Macro how would I create and name new Worksheets named after each Row i.e 201-NEB, 202-NEB 203-NEB, 204-NWB, 205-NWB, 209-NWB. Its simple to do it manually from the above example, but what if I wanted to create 30 worksheets named after each row in Worksheet 1 Once I created a Worksheet... Using Macro or Formula, How would I create a new Column in Worksheet 1, which extracts value from a specific cell from each Worksheet using the Columns above. I attempted using the formula below but with no joy..... ="=""'"&A3&"-"&B3&"'"&"!"&"U8" This result only returns '='203-NEB'!U8 not the value in Cell U8 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Names using Macros
Add the sheets macro.
Assumes the names are in Sheet1 Sub Add_Sheets() Dim rCell As Range Dim Rng As Range Set Rng = Range(Cells(1), Cells(Rows.Count, 1).End(xlUp)) For Each rCell In Rng With Worksheets.Add(after:=Worksheets(Worksheets.Count) ) .Name = rCell.Value & "-" & rCell.Offset(0, 1).Value End With Next rCell End Sub Formula to go into D1 on Sheet1 =INDIRECT("'" & A1 &"-"& B1 & "'!U8") Gord On Mon, 20 Feb 2012 15:48:52 -0800 (PST), Yunus Patel wrote: Hi I have 2 Columns in Worksheet.... A B 201 NEB 202 NEB 203 NEB 204 NWB 205 NWB 209 NWB Using formula or Macro how would I create and name new Worksheets named after each Row i.e 201-NEB, 202-NEB 203-NEB, 204-NWB, 205-NWB, 209-NWB. Its simple to do it manually from the above example, but what if I wanted to create 30 worksheets named after each row in Worksheet 1 Once I created a Worksheet... Using Macro or Formula, How would I create a new Column in Worksheet 1, which extracts value from a specific cell from each Worksheet using the Columns above. I attempted using the formula below but with no joy..... ="=""'"&A3&"-"&B3&"'"&"!"&"U8" This result only returns '='203-NEB'!U8 not the value in Cell U8 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Names using Macros
On Feb 21, 1:01*am, Gord Dibben wrote:
Add the sheets macro. Assumes the names are in Sheet1 Sub Add_Sheets() Dim rCell As Range Dim Rng As Range Set Rng = Range(Cells(1), Cells(Rows.Count, 1).End(xlUp)) For Each rCell In Rng * * With Worksheets.Add(after:=Worksheets(Worksheets.Count) ) * * * * .Name = rCell.Value & "-" & rCell.Offset(0, 1).Value * * End With Next rCell End Sub Formula to go into D1 on Sheet1 =INDIRECT("'" & A1 &"-"& B1 & "'!U8") Gord On Mon, 20 Feb 2012 15:48:52 -0800 (PST), Yunus Patel wrote: Hi I have 2 Columns in Worksheet.... A * * *B 201 *NEB 202 *NEB 203 *NEB 204 *NWB 205 *NWB 209 *NWB Using formula or Macro how would I create and name new Worksheets named after each Row i.e 201-NEB, 202-NEB 203-NEB, 204-NWB, 205-NWB, 209-NWB. Its simple to do it manually from the above example, but what if I wanted to create 30 worksheets named after each row in Worksheet 1 Once I created a Worksheet... Using Macro or Formula, How would I create a new Column in Worksheet 1, which extracts value from a specific cell from each Worksheet using the Columns above. I attempted using the formula below but with no joy..... ="=""'"&A3&"-"&B3&"'"&"!"&"U8" This result only returns '='203-NEB'!U8 not the value in Cell U8- Hide quoted text - - Show quoted text - Thanks Gord The Macro only works for an existing list, what if I want to update or add to that list to rename or create more worksheets |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Names using Macros
To add more sheets simply tack them onto the bottom of the existing
list, select the new ones and run this macro. NOTE: Do not select existing names or blanks cells. Sub Add_More_Sheets() Dim rCell As Range Dim Rng As Range Set Rng = Selection 'select just added names in column A For Each rCell In Rng With Worksheets.Add(after:=Worksheets(Worksheets.Count) ) .Name = rCell.Value & "-" & rCell.Offset(0, 1).Value End With Next rCell End Sub As far as changing sheet names how many and what would you change the names to? Hard to tailor something like that. Probably better done manually unless you want to change a great lot of them at one go. Give me an idea of what renaming would consist of. NOTE: If you do rename a sheet the INDIRECT formulas you dragged down column D will not have to be edited..............they will pick up the new name. Gord On Tue, 21 Feb 2012 16:23:43 -0800 (PST), Yunus Patel wrote: On Feb 21, 1:01*am, Gord Dibben wrote: Add the sheets macro. Assumes the names are in Sheet1 Sub Add_Sheets() Dim rCell As Range Dim Rng As Range Set Rng = Range(Cells(1), Cells(Rows.Count, 1).End(xlUp)) For Each rCell In Rng * * With Worksheets.Add(after:=Worksheets(Worksheets.Count) ) * * * * .Name = rCell.Value & "-" & rCell.Offset(0, 1).Value * * End With Next rCell End Sub Formula to go into D1 on Sheet1 =INDIRECT("'" & A1 &"-"& B1 & "'!U8") Gord On Mon, 20 Feb 2012 15:48:52 -0800 (PST), Yunus Patel wrote: Hi I have 2 Columns in Worksheet.... A * * *B 201 *NEB 202 *NEB 203 *NEB 204 *NWB 205 *NWB 209 *NWB Using formula or Macro how would I create and name new Worksheets named after each Row i.e 201-NEB, 202-NEB 203-NEB, 204-NWB, 205-NWB, 209-NWB. Its simple to do it manually from the above example, but what if I wanted to create 30 worksheets named after each row in Worksheet 1 Once I created a Worksheet... Using Macro or Formula, How would I create a new Column in Worksheet 1, which extracts value from a specific cell from each Worksheet using the Columns above. I attempted using the formula below but with no joy..... ="=""'"&A3&"-"&B3&"'"&"!"&"U8" This result only returns '='203-NEB'!U8 not the value in Cell U8- Hide quoted text - - Show quoted text - Thanks Gord The Macro only works for an existing list, what if I want to update or add to that list to rename or create more worksheets |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Names using Macros
On Feb 22, 1:38*am, Gord Dibben wrote:
To add more sheets simply tack them onto the bottom of the existing list, select the new ones and run this macro. NOTE: *Do not select existing names or blanks cells. Sub Add_More_Sheets() Dim rCell As Range Dim Rng As Range Set Rng = Selection * 'select just added names in column A For Each rCell In Rng * * With Worksheets.Add(after:=Worksheets(Worksheets.Count) ) * * * * .Name = rCell.Value & "-" & rCell.Offset(0, 1).Value * * End With Next rCell End Sub As far as changing sheet names how many and what would you change the names to? Hard to tailor something like that. *Probably better done manually unless you want to change a great lot of them at one go. Give me an idea of what renaming would consist of. NOTE: *If you do rename a sheet the INDIRECT formulas you dragged down column D will not have to be edited..............they will pick up the new name. Gord On Tue, 21 Feb 2012 16:23:43 -0800 (PST), Yunus Patel wrote: On Feb 21, 1:01*am, Gord Dibben wrote: Add the sheets macro. Assumes the names are in Sheet1 Sub Add_Sheets() Dim rCell As Range Dim Rng As Range Set Rng = Range(Cells(1), Cells(Rows.Count, 1).End(xlUp)) For Each rCell In Rng * * With Worksheets.Add(after:=Worksheets(Worksheets.Count) ) * * * * .Name = rCell.Value & "-" & rCell.Offset(0, 1).Value * * End With Next rCell End Sub Formula to go into D1 on Sheet1 =INDIRECT("'" & A1 &"-"& B1 & "'!U8") Gord On Mon, 20 Feb 2012 15:48:52 -0800 (PST), Yunus Patel wrote: Hi I have 2 Columns in Worksheet.... A * * *B 201 *NEB 202 *NEB 203 *NEB 204 *NWB 205 *NWB 209 *NWB Using formula or Macro how would I create and name new Worksheets named after each Row i.e 201-NEB, 202-NEB 203-NEB, 204-NWB, 205-NWB, 209-NWB. Its simple to do it manually from the above example, but what if I wanted to create 30 worksheets named after each row in Worksheet 1 Once I created a Worksheet... Using Macro or Formula, How would I create a new Column in Worksheet 1, which extracts value from a specific cell from each Worksheet using the Columns above. I attempted using the formula below but with no joy..... ="=""'"&A3&"-"&B3&"'"&"!"&"U8" This result only returns '='203-NEB'!U8 not the value in Cell U8- Hide quoted text - - Show quoted text - Thanks Gord The Macro only works for an existing list, what if *I want to update or add to that list to rename or create more worksheets- Hide quoted text - - Show quoted text - Gord, Is there any chance of doing the same but this time instaed of adding a new worksheet, it copies Worksheet X and then names it as per names in Sheet 1. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Names using Macros
Have a look at this macro from Dave Peterson.
Put your names in column A in sheet "List". Name the sheet to copy as "Template" or your choice. The macro will copy that sheet as many times as you have names in "List" sheet. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: Template ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("Template") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy after:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord On Mon, 27 Feb 2012 02:07:44 -0800 (PST), Yunus Patel wrote: On Feb 22, 1:38*am, Gord Dibben wrote: To add more sheets simply tack them onto the bottom of the existing list, select the new ones and run this macro. NOTE: *Do not select existing names or blanks cells. Sub Add_More_Sheets() Dim rCell As Range Dim Rng As Range Set Rng = Selection * 'select just added names in column A For Each rCell In Rng * * With Worksheets.Add(after:=Worksheets(Worksheets.Count) ) * * * * .Name = rCell.Value & "-" & rCell.Offset(0, 1).Value * * End With Next rCell End Sub As far as changing sheet names how many and what would you change the names to? Hard to tailor something like that. *Probably better done manually unless you want to change a great lot of them at one go. Give me an idea of what renaming would consist of. NOTE: *If you do rename a sheet the INDIRECT formulas you dragged down column D will not have to be edited..............they will pick up the new name. Gord On Tue, 21 Feb 2012 16:23:43 -0800 (PST), Yunus Patel wrote: On Feb 21, 1:01*am, Gord Dibben wrote: Add the sheets macro. Assumes the names are in Sheet1 Sub Add_Sheets() Dim rCell As Range Dim Rng As Range Set Rng = Range(Cells(1), Cells(Rows.Count, 1).End(xlUp)) For Each rCell In Rng * * With Worksheets.Add(after:=Worksheets(Worksheets.Count) ) * * * * .Name = rCell.Value & "-" & rCell.Offset(0, 1).Value * * End With Next rCell End Sub Formula to go into D1 on Sheet1 =INDIRECT("'" & A1 &"-"& B1 & "'!U8") Gord On Mon, 20 Feb 2012 15:48:52 -0800 (PST), Yunus Patel wrote: Hi I have 2 Columns in Worksheet.... A * * *B 201 *NEB 202 *NEB 203 *NEB 204 *NWB 205 *NWB 209 *NWB Using formula or Macro how would I create and name new Worksheets named after each Row i.e 201-NEB, 202-NEB 203-NEB, 204-NWB, 205-NWB, 209-NWB. Its simple to do it manually from the above example, but what if I wanted to create 30 worksheets named after each row in Worksheet 1 Once I created a Worksheet... Using Macro or Formula, How would I create a new Column in Worksheet 1, which extracts value from a specific cell from each Worksheet using the Columns above. I attempted using the formula below but with no joy..... ="=""'"&A3&"-"&B3&"'"&"!"&"U8" This result only returns '='203-NEB'!U8 not the value in Cell U8- Hide quoted text - - Show quoted text - Thanks Gord The Macro only works for an existing list, what if *I want to update or add to that list to rename or create more worksheets- Hide quoted text - - Show quoted text - Gord, Is there any chance of doing the same but this time instaed of adding a new worksheet, it copies Worksheet X and then names it as per names in Sheet 1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
how to copy workbook names and worksheet names to columns in acces | Excel Programming | |||
Can you use worksheet names in macros | Excel Programming | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming |