macro to change tab names

[rhombus4]

IS it possible to run a Macro that will change the names of Tabs

i.e. if you have Sheet1-Sheet5 then In Sheet6 starting in A1-A5 the names of people.

When you ran the macro what was in cell A1 becomes the name of Sheet1 A2 becomes the name of Sheet2 and so on

 

[lenze]

Per your example, you can use something like this

Sub NameShts()
Dim cl As Range
For Each cl In Sheet6.Range("$A$1:$A$5")
Sheets(cl.Row).Name = cl
Next cl
End Sub

lenze

 

[rhombus4]

Per your example, you can use something like this

Sub NameShts()
Dim cl As Range
For Each cl In Sheet6.Range("$A$1:$A$5")
Sheets(cl.Row).Name = cl
Next cl
End Sub

lenze

Gives me a Run time error 1004 application - defined or object-defined error, then when I click debug highlights
Sheets(cl.Row).Name = cl

 

[lenze]

Don't know!! It runs fine for me!!

lenze

Edit: Are all of your entries are valid sheet names? Perhaps an error trap may be needed!!

On Error Resume Next

 

[rhombus4]

Don't know!! It runs fine for me!!

lenze

Edit: Are all of your entries are valid sheet names? Perhaps an error trap may be needed!!

On Error Resume Next

Opened New Excel Sheet and created sheets 1 - 6 and added macro but get following error

Get run time Error 424

Sub NameShts()
Dim cl As Range
For Each cl In Sheet6.Range("$A$1:$A$5")
Sheets(cl.Row).Name = cl
Next cl
On Error Resume Next
End Sub

 

[lenze]

What are the names you want. I don't think they are valid!!. You placed the On Error statement in the wrong place

Sub NameShts()
Dim cl As Range
On Error Resume Next
For Each cl In Sheet6.Range("$A$1:$A$5")
Sheets(cl.Row).Name = cl
Next cl
End Sub

I've tested this several times and can not reproduce an error. It runs as expected!!
lenze<!-- / message -->

 

[rhombus4]

What are the names you want. I don't think they are valid!!. You placed the On Error statement in the wrong place

Sub NameShts()
Dim cl As Range
On Error Resume Next
For Each cl In Sheet6.Range("$A$1:$A$5")
Sheets(cl.Row).Name = cl
Next cl
End Sub

I've tested this several times and can not reproduce an error. It runs as expected!!
lenze<!-- / message -->

Thanks. Not sure what went wrong I tried it again and i got an error then i ran it again and it worked.

So created a new book and did same thing and it works fine!