Worksheet Names using Macros

[Yunus Patel]

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

 

[Gord Dibben]

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

 

[Yunus Patel]

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








- 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

 

[Gord Dibben]

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

 

[Yunus Patel]

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




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

 

[Gord Dibben]

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