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.
|