Can I batch rename new worksheets

  • Thread starter Thread starter Harry Limey
  • Start date Start date
H

Harry Limey

Is there any way to create and name new worksheets using names in an
existing database? or having created the sheets - to rename them in bulk??
TIA
 
Hi Harry

Try this one with the list in Range("A2:A100") of "Sheet1"

Sub test()
Dim cell As Range
Dim WSNew As Worksheet

For Each cell In Sheets("Sheet1").Range("A2:A100").SpecialCells(xlCellTypeConstants)
Set WSNew = Worksheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0

Next cell
End Sub
 
Ron

Works perfectly - did you just write that or have it on file??
either way, I will be using that again.
Many thanks

Harry
 
Back
Top