create & name multiple worksheets

O

OrlaH

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
 
D

Don Guillett

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

OrlaH

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
 
D

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
 
O

OrlaH

tremendous Don. thanks for all your help.

Don Guillett said:
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
Click to expand...
 
Top