rename sheets

[kendel]

Hello again, I'm using WinXP and Excel 2007
I've read many pages of the 'rename sheets' search results and have a macro that will rename sheets based on a list. But it always starts with the 1st sheet in the workbook, regardless of where the sheet with the list is located in the workbook. How can I select the sheet that I want to start the renaming from?

sub change_names()
dim s as long, i as long
s=sheets.count
i=1
for each cell in selection
if i>s then exit sub
thisWS=cell.value
sheets(i).name=thisWS
i=i+1
next cell
end sub

thanks in advance for any help with this

 

[kendel]

Thank you all for your responses!

Norie, hello. I would like to select the sheet to start the renaming by clicking on it, seems easier than having to adjust the code.

Domenic, thanks again. I'd like to not have to list 240 old names and then the 240 new names lol! Is my list of old worksheet names in col A and new names in col B? I tried that, and new names in A with old names in B and both codes get stuck in the same place,
Sheet(cell1.Value),Name=thisWS.

SamTYler, hello. There are 15 sheets, 11 hidden, that I dont want to change, but moving them to the end of the workbook and back sounds alot easier than excluding them in the code.
I'm the Production Supv and we have a process of recording team metrics daily. I had started to tell you about this process but it would probably just confuse everybody.
I know how to change the names I just want to be able to click on the sheet that will be the first, so I don't have to unhide, group, move, then move back and hide again. It's not much of a problem but I'm thinking that being able to just click on a sheet and start renaming a large number of sheets from that point would be a good thing to know.

Thank you to everybody!

 

[Domenic]

I'd like to not have to list 240 old names and then the 240 new names

Where will the new names come from?

 

[kendel]

Hello everybody, thanks again for all responses I really appreciate it!
I'll 'make it work'

 

[SamTYler]

Using the list of rename names you have in col A and Col B

Sub Rename_My_Sheets()
Dim cel As Range
For Each cel In Selection ' Select entire list in Column A only
Sheets(cel.Text).Name = cel.Offset(0,1).Text 
Next cel
End Sub

There are 15 sheets, 11 hidden, that I dont want to change, but moving them to the end of the workbook and back sounds alot easier than excluding them in the code.

Tip of the day: Write repetitive VBA code in Worksheet

In new sheet
"A1":= Dim NoRename(1 to 15 As String)
"A2":= NoRename(
Copy Down to "A16"

"B2":= 1
Fill Down to "B16"

"C2":= )= " "E2":= "
Copy Down to "B16:E16"

D2 to D16:= the sheets to NOT rename

Declare a function

Function Exclude(SheetName As String) As Boolean

Then Copy Range "A1" to "E16" and paste into the function.

Then paste this into the function under the above

For j = 1 to 15
If NoRename(j) = SheetName Then
Exclude = True
Exit Function
End IF
Next j
Exclude = False

 

[kendel]

Thank you Sam!