CODEWITHSUNDEEP
vbaexcel
daviiid
daviiid

Reputation: 15

Rename sheets based on lookup table

I'm new to this so forgive me if I don't post this correctly.

I have a Excel file which contains various sheets, some of which are named V1, V2, ... up to a potential max of V15.

I have a table in 'Sheet names' cells B2:C16:
Column B contains the current sheet names V1-V15.
Column C contains the new sheet names/labels.

Are you able to offer some VBA code to be able to rename the sheets currently called V1 up to a potential max of V15 to the new values?

I've found quite a bit of stuff based on naming sheets from a list, but not in a vlookup style.

Sub RenSheets()  
Dim i As Integer  
For i = 2 To Worksheets.Count  
    Sheets(i).Name = Sheets("Sheet names").Range("C" & 2 + i)  
Next i  
End Sub

Thanks very much

Upvotes: 1

Views: 1125

Answers (2)

Tom
Tom

Reputation: 9878

This is how I'd look at starting it. Instead of looping through the worksheets and then trying to find the answer in your table, If you loop through the table and select the sheets (you can change the table location and size by modifying the set rng statement) This will then rename all of the worksheets that are detailed in that range. 

Sub RenSheets()  
    Dim rng as Range
    Dim c

    set rng = ThisWorkbook.Sheets("Sheet names").Range("B2:B16")

    For each c in rng.cells  
        ThisWorkbook.Sheets(c.value).Name = c.offset(0,1).value  
    Next c  
End Sub

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

try to not use Sheets(i).Name... if the sheet-order changed, you may get trouble

Sub RenSheets()
  Dim i As Integer
  For i = 2 To 16 'Your Range = B2:C16
    Sheets(Sheets("Sheet names").Cells(i, 2)).Name = _ 'sheet to rename
    Sheets("Sheet names").Cells(i, 3) 'new sheetname
  Next
End Sub

Upvotes: 0

Related Questions