Using Table data to automatically (re)name worksheets in Excel 2007?

May 10, 2011 2:19 PM

In Excel 2007 can I use data in a column on one spreadsheet to automatically change the name of another spreadsheet in the same workbook?

Say I have a workbook with 11 sheets. The first sheet in the workbook is called "Key". It has two columns labeled "Numbers" and "Letters". "Numbers" has the numbers from 1 to 10 sequentially in its first ten rows, and "Letters" has the corresponding letter of the alphabet (A-J). The remaining ten sheets in the workbook are labeled "1" to "10". Is there an automated way to change them to the corresponding letter value using the data in the first worksheet?

posted by Lentrohamsanin to Computers & Internet (2 answers total)

This VBA code will change the 2nd through 11th sheet's names to2, 3, 4, etc. You can change the i on the right side to correspond to the numbers column.


Sub change_sheet_names()

For i = 2 To 11 Step 1

Worksheets("Sheet" & i).Name = i

Next i

End Sub
posted by amsterdam63 at 2:30 PM on May 10, 2011

Or this does it, assuming "key" is the name of the first sheet, and you add 10 new sheets that are initially named default, Sheet2, Sheet3, etc.

Then in "key" just put some values in A1:A10, then running the macro will change the names of the rest of the sheets.


Sub change_sheet_names()

For i = 2 To 11 Step 1

Worksheets("Sheet" & i).Name = Worksheets("Key").Range("a" & (i - 1)).Value

Next i

End Sub
posted by amsterdam63 at 2:35 PM on May 10, 2011