Rename a sheet based on data in a cell

[Marc_w90]

I'm trying to relearn some VBA and hoping someone can help me out.
I'm trying to make a macro that will do the following:

I want the macro to change the old sheet names in column A to the new sheet names on the column B
Column A has a list of current sheet names (length of column unknown but maximum of 100 lines)
Column B has a list of new sheet names
If Column B is blank, leave the sheet name as it is.

Thanks

 

[bebo021999]

Like this:
Assume list of sheets name start from A1, all the way down:

Option Explicit
Sub rename()
Dim lr&, cell As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A1:A" & lr)
    If Evaluate("=ISREF(" & cell.Value & "!A1)") Then
        Sheets(cell.Value).Name = IIf(IsEmpty(cell.Offset(, 1)), cell.Value, cell.Offset(, 1).Value)
    End If
Next
End Sub

 

[Marc_w90]

Hi,
Thanks for this.

Having a few issues and not really sure why.

I have the list of 33 sheet names (starting in A1 like you suggested) and I have TEST1, TEST2 etc running along side in column B.

For some reason, when I run the macro above, it changes lines 24-27 but non of the others.
I know the names of the sheets are correct because I used another macro to extract them.
Can you suggest any reasons why this might happen?

Thanks

 

[Marc_w90]

Hi,
Thanks for this.

Having a few issues and not really sure why.

I have the list of 33 sheet names (starting in A1 like you suggested) and I have TEST1, TEST2 etc running along side in column B.

For some reason, when I run the macro above, it changes lines 24-27 but non of the others.
I know the names of the sheets are correct because I used another macro to extract them.
Can you suggest any reasons why this might happen?

Thanks

Partially solved my own problem, for some reason the code above doesn't like Sheet names with any space in it anywhere.

Is there a way we can allow for spaces in sheet names?

Thanks

 

[Fluff]

You would need to use

If Evaluate("=ISREF('" & cell.Value & "'!A1)") Then