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




kefee85 wrote:

I have a list of dates in A1:A15 I would automatically like to name sheets
3-18 after the values in that range.

--

Dave Peterson

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

"kefee85" wrote:

I have a list of dates in A1:A15 I would automatically like to name sheets
3-18 after the values in that range.

Er,

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

Doh!

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

Dave Peterson wrote:

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

kefee85 wrote:

I have a list of dates in A1:A15 I would automatically like to name sheets
3-18 after the values in that range.

--

Dave Peterson

--

Dave Peterson