ExcelBanter - create & name multiple worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - create & name multiple worksheets (https://www.excelbanter.com/excel-worksheet-functions/92686-create-name-multiple-worksheets.html)

create & name multiple worksheets

Hi:
I have a row of 20 numbers.
I want to create a worksheet for each number and name the worksheet after
that number.

I have a little exposure to macros.

OrlaH

create & name multiple worksheets

thanks for the response Don,

I'll try that. The only thing is my range of numbers is not sequential,
e.g. 13, 16, 43, 55. The numbers will always be in a set column, i.e. A:A so
how do I replace the "for i = 4 to 24" with what I want. Would for i = A:A
work?

Kind regards
OrlaH

"Don Guillett" wrote:

like this?
Sub newsheets()
For i = 4 To 24
Sheets.Add
ActiveSheet.Name = i
Next
End Sub

--
Don Guillett
SalesAid Software

"OrlaH" wrote in message
...
Hi:
I have a row of 20 numbers.
I want to create a worksheet for each number and name the worksheet after
that number.

I have a little exposure to macros.

OrlaH

create & name multiple worksheets

You could use this but then you would have to sort the sheets so use the 2nd
one.
Sub newsheetsfromlist()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each i In Range("a2:a" & lr)
Sheets.Add
ActiveSheet.Name = i
Next
End Sub

use the one below
Sub newsheetsfromlistBottomUp()
With Sheets("sheet1")
lr = .Cells(Rows.Count, "a").End(xlUp).Row
For i = lr To 2 Step -1
Sheets.Add
ActiveSheet.Name = .Cells(i, "a")
Next
End With
End Sub

or probably BEST
Sub newsheetsAfter()
With Sheets("sheet1")
lr = .Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lr
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = .Cells(i, "a")
Next
End With
End Sub

--
Don Guillett
SalesAid Software

"OrlaH" wrote in message
...
thanks for the response Don,

I'll try that. The only thing is my range of numbers is not sequential,
e.g. 13, 16, 43, 55. The numbers will always be in a set column, i.e. A:A
so
how do I replace the "for i = 4 to 24" with what I want. Would for i =
A:A
work?

Kind regards
OrlaH

"Don Guillett" wrote:

like this?
Sub newsheets()
For i = 4 To 24
Sheets.Add
ActiveSheet.Name = i
Next
End Sub

--
Don Guillett
SalesAid Software

"OrlaH" wrote in message
...
Hi:
I have a row of 20 numbers.
I want to create a worksheet for each number and name the worksheet
after
that number.

I have a little exposure to macros.

OrlaH

create & name multiple worksheets

tremendous Don. thanks for all your help.

"Don Guillett" wrote:

You could use this but then you would have to sort the sheets so use the 2nd
one.
Sub newsheetsfromlist()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each i In Range("a2:a" & lr)
Sheets.Add
ActiveSheet.Name = i
Next
End Sub

use the one below
Sub newsheetsfromlistBottomUp()
With Sheets("sheet1")
lr = .Cells(Rows.Count, "a").End(xlUp).Row
For i = lr To 2 Step -1
Sheets.Add
ActiveSheet.Name = .Cells(i, "a")
Next
End With
End Sub

or probably BEST
Sub newsheetsAfter()
With Sheets("sheet1")
lr = .Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lr
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = .Cells(i, "a")
Next
End With
End Sub

--
Don Guillett
SalesAid Software

"OrlaH" wrote in message
...
thanks for the response Don,

I'll try that. The only thing is my range of numbers is not sequential,
e.g. 13, 16, 43, 55. The numbers will always be in a set column, i.e. A:A
so
how do I replace the "for i = 4 to 24" with what I want. Would for i =
A:A
work?

Kind regards
OrlaH

"Don Guillett" wrote:

like this?
Sub newsheets()
For i = 4 To 24
Sheets.Add
ActiveSheet.Name = i
Next
End Sub

--
Don Guillett
SalesAid Software

"OrlaH" wrote in message
...
Hi:
I have a row of 20 numbers.
I want to create a worksheet for each number and name the worksheet
after
that number.

I have a little exposure to macros.

OrlaH

create & name multiple worksheets