Re-Name Worksheets w/ Macro?

Hi, samuelabyrd,

do you have one cell showing the new number or is it a range in which these names are to be found?

Sub EF940093_OneCell()
Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets
  ws.Name = ws.Range("A1").Value
  If Err <> 0 Then
    MsgBox "Check the value in '" & ws.Name & "' please.", vbExclamation, "Error renaming sheet"
    Exit Sub
  End If
Next ws
End Sub

Sub EF940093_RunningCells()
Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets
  ws.Name = ws.Cells(ws.Index, "A").Value
  If Err <> 0 Then
    MsgBox "Check the value in '" & ws.Name & "' please.", vbExclamation, "Error renaming sheet"
    Exit Sub
  End If
Next ws
End Sub

Ciao,
Holger

Hi, samuelabyrd,

and then: range on one special sheet or on any? The Index of a Worksheet will always start with 1, so we need to add at least 4 to the Index to match the range.

Sub EF940093_RunningCells_Mod()
Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets
  ws.Name = ws.Cells(ws.Index + 4, "A").Value
  If Err <> 0 Then
    MsgBox "Check the value in '" & ws.Name & "' please.", vbExclamation, "Error renaming sheet"
    Exit Sub
  End If
Next ws
End Sub

Sub EF940093_RunningCells_OneSheet()
Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets
  ws.Name = Sheets("Index").Cells(ws.Index + 4, "A").Value
  If Err <> 0 Then
    MsgBox "Check the value in Cell '" & Cells(ws.Index + 4, "A").Address(0, 0) & "' please.", vbExclamation, "Error renaming sheet"
    Exit Sub
  End If
Next ws
End Sub

Ciao,
Holger

Hi Holger,

Yes the range of information is on a worksheet titled "House Codes".

When I run it, is says, "Check the value in cell 'A5' please." The value in the cell is a numeric text entry, 25045 in this case.

Thank you.

Hi, samuelabyrd,

maybe itīs that the sheet with the codes is renamed and thus not found any more.

Please give the updated version a try:

Sub EF940093_RunningCells_OneSheet_2()
Dim ws As Worksheet
Dim lngCounter As Long
lngCounter = 5

On Error Resume Next
For Each ws In Worksheets
  If ws.Name <> "House Codes" Then
    ws.Name = Sheets("House Codes").Cells(lngCounter, "A").Value
    If Err <> 0 Then
      MsgBox "Check the value in Cell '" & Cells(lngCounter, "A").Address(0, 0) & "' please.", vbExclamation, "Error renaming sheet"
      Exit Sub
    End If
    lngCounter = lngCounter + 1
  End If
Next ws

End Sub

Ciao,
Holger