Renaming Worksheets 2

[FJAY]

Good Afternoon - I have a workbook with 20 worksheets and will like to re-name the worksheets with the values in a range i.e A1:A20 with the click of a command button. How do I do this?

Code example:

dim ws as worksheet, rng as range

sheets("Sheet1").select
sheets("Sheet1").range("A1:A20").select

for each ws.worksheets
for each rng in selection.cells
ws.name = rng
next
next

 

[calculus]

I would go more like this.

For Each Sht in ws.sheets
sht.Name = Cells(sht.Index, 1).Value
Next Sht

You'll have to know the offset in the range to use it in an X,Y format for Cells(X,Y), where the "1" is the column number.

calculus

 

[xlbo]

dim tSht as worksheet
set tSht = sheets("Sheet1")

for i = 1 to worksheets.count
 sheets(i).name = tSht.cells(i,1).value
next i
set tSht = nothing

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question