Rename Worksheets Based on Two Named Ranges

[bkanne]

Hi there, I've attached a sample workbook which should clearly illustrate the problem I am trying to solve.

I'd like to be able to rename the individual worksheets in a workbook based on two lists (shown in the same file). I've named both of these ranges.

Can someone please help me write a macro that will rename all the sheets in the workbook that are listed in the range (but also leave the names not mentioned in the range untouched)?

Please let me know of any questions and thanks so much as always!

 

[chirayu]

try this in your file

Sub SheetRenamer()

Dim Rng As Range
Dim SubRng As Range
Dim LstRow As Integer

With Worksheets("HOME")
    LstRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set Rng = .Range("$C$11:$C$" & LstRow)
End With

For Each SubRng In Rng
    ActiveWorkbook.Worksheets(SubRng.Text).Name = SubRng.Offset(0, 2)
Next SubRng


End Sub

 

[Chihiro]

Something like below?

Sub Demo()
Dim i as Long
With Range("CURRENT_Names")
    On Error Resume Next
    For i = 1 To .Rows.Count
        Worksheets(.Cells(i, 1).Value).Name = Range("Desired_Names").Cells(i, 1).Value
    Next
End With

End Sub

 

[bkanne]

These are both great.

Thank you so much.