Rename Sheet tabs from list......

D

DanaK

I've read every posting I could find for renaming sheet tabs, but haven't
stumbled upon a solution. I want to take a list on a worksheet and from that
list (in sequential order A1-A100), rename every tab in the workbook. Is this
possible?
 
M

Mike

Sub reNameSheets()
Const listColumn As String = "A"
Dim ws As Worksheet
Dim SheetCount As Integer
Dim listStartingRow As Long
SheetCount = ActiveWorkbook.Worksheets.Count
listStartingRow = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then
Worksheets(ws.Name).Name = Range(listColumn & i).Value
SheetCount = SheetCount + 1
listStartingRow = listStartingRow + 1
End If
Next
End Sub
 
M

Mike

Sub reNameSheets()
Const listColumn As String = "A"
Dim ws As Worksheet
Dim SheetCount As Integer
Dim listStartingRow As Long
SheetCount = ActiveWorkbook.Worksheets.Count
listStartingRow = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then
Worksheets(ws.Name).Name = Range(listColumn &
listStartingRow).Value
SheetCount = SheetCount + 1
listStartingRow = listStartingRow + 1
End If
Next
End Sub
 
D

Dave Peterson

Maybe something like:

Option Explicit
Sub testme()

Dim iRow as long
with activesheet
for irow = 1 to .cells(.rows.count,"A").end(xlup).row
sheets(irow).name = .cells(irow,"A").value
next irow
end with

End with



This could fail if you have invalid names in column A and/or there are duplicate
names or sheets with the same name already existing. Or if you don't have
enough sheets!
 
D

DanaK

Fellas! Thank, thank you! It's working perfect. I am learning SO much
here....you are the best!
 
S

shg

Most simply, and assuming the list of new sheet names is on the activ
sheet and that the sheets to be renamed are those in the activ
workbook

Code
-------------------
Sub x()
Dim i As Long

For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Name = ActiveSheet.Cells(i, "A").Text
Next i
End Sub
 
Top