ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Worksheet Names using Macros (https://www.excelbanter.com/excel-worksheet-functions/406325-worksheet-names-using-macros.html)

Yunus Patel
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

Gord Dibben[_2_]
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

Yunus Patel
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

Gord Dibben[_2_]
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

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

Gord Dibben[_2_]
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.


All times are GMT +1. The time now is 11:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com