create & name multiple worksheets

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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]

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

 

[Guest]

tremendous Don. thanks for all your help.

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]

glad to help