Naming sheets after a range

  • Thread starter Thread starter kefee85
  • Start date Start date
K

kefee85

I have a list of dates in A1:A15 I would automatically like to name sheets
3-18 after the values in that range.
 
There's a mismatch between the range and the number of sheets.

A1:A15 is 14 cells.
3-18 is 15 sheets.

You may have to adjust the "for irow = 3 to 18" line.


Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long

Set wks = ActiveSheet

If Sheets.Count < 18 Then
MsgBox "not enough sheets"
Exit Sub
End If

For iRow = 3 To 18
On Error Resume Next
Sheets(iRow).Name = wks.Cells(iRow - 2, "A").Value
If Err.Number <> 0 Then
Err.Clear
MsgBox "Rename " & Sheets(iRow - 2).Name & " manually"
End If
On Error GoTo 0
Next iRow

End Sub
 
Hi,

Alt + f11 to open VB editor, right click 'This Wirkbook' and insert module.
Paste the code below in and run it.

Sub rename()
x = 3
Set MyRange = Sheets("Sheet1").Range("A1:A15")
For Each c In MyRange
Sheets(x).Name = Format(c.Value, "dd-mm-yyyy")
x = x + 1
Next
End Sub

Mike
 
Er,

a1:a15 is 15 cells
3-18 is 16 sheets

Doh!

And you may have to change the sheets.count line, too.
 
Back
Top