Automatic sheet naming
 

Is it possible with a macro to have all let's say 20
names on a first sheet and use them as a base to name 20
new to create sheet's?

Like:

x = A2
Sheets("Sheet2").Name = x

And so on

Thanks in advance,
Hans Weustink

Automatic sheet naming
 

This code will loop through a column of data and create a new sheet name
from the data.

It does not check for length or invalid name characters.


Sub MakeNamedSheets()

' Loop through column where sheet names are stored
For Each cell In Sheet1.Range("A1:A65536")
If cell = "" Then Exit For
' Add a New Sheet
Sheets.Add
Sheets(ActiveSheet.Name).Name = cell
Next cell

End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Automatic sheet naming
 

Thank you Barry!

It works great.

-----Original Message-----
This code will loop through a column of data and create
a new sheet name
from the data.

It does not check for length or invalid name characters.


Sub MakeNamedSheets()

' Loop through column where sheet names are stored
For Each cell In Sheet1.Range("A1:A65536")
If cell = "" Then Exit For
' Add a New Sheet
Sheets.Add
Sheets(ActiveSheet.Name).Name = cell
Next cell

End Sub


*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!
.

Automatic sheet naming
 

Building on Barry's code to do the checking

Sub MakeNamedSheets()
Dim TempName as String
' Loop through column where sheet names are stored
For Each cell In Sheet1.Range("A1:A65536")
TempName = Cell.Text
If TempName = "" Then Exit For 'sub aborted as end of names block
' Add a New Sheet
Sheets.Add
If Len(TempName) 30 Then
TempName = Left(TempName, 10) & " ... " & Right(TempName, 3)
'length of 18
End If
'Finally, check TempName does not contain :, \, /, ?, *, [ or ]
Do While (InStr(TempName, ":") Or InStr(TempName, "\") Or _
InStr(TempName, "/") Or InStr(TempName, "?") Or _
InStr(TempName, "*") Or InStr(TempName, "[") Or _
InStr(TempName, "]"))
TempName = InputBox("Sheet name contains :, \, /, ?, *, [ or ]." _
& "Please edit to remove.","Title", TempName)
Loop
cell.Value = TempName 'optional
Sheets(ActiveSheet.Name).Name = TempName
Next cell

End Sub

regards
Paul