rename sheets

kendel

Board Regular
Joined
Mar 2, 2010
Messages
133
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Is this what you mean? Enter the sheet names in the desired order, within the array assigned to the variable 'x'. Then, run the code. It renames these sheets in the specified order, according to the names in the selection...

Code:
Option Explicit

Sub change_names()

    Dim x As Variant
    Dim cell As Range
    Dim i As Long
    
    x = Array("Sheet2", "Sheet3", "Sheet1")  [COLOR="SeaGreen"]'Change and/or add the sheet names in the desired order[/COLOR]
    
    i = 0
    For Each cell In Selection
        If i > UBound(x) Then Exit Sub
        Sheets(x(i)).Name = cell
        i = i + 1
    Next cell
    
End Sub
 
Upvote 0
hello, thanks for the response. but that code gets stuck at the
sheets(x(i)).name=cell line.
I think I don't know how to use this code, will I have to manually input the x=array("sheet2",... for the 60 sheets I want to rename in the macro itself?
I'd like to be able to choose which sheet the program starts from. Try my code and see if you can tweak it to be able to choose where it starts,
thanks again!
 
Upvote 0
How would you like to be able to choose which sheets to rename? If, for example, you'd like to be able to list the sheets in a column, let's say Column A, starting at A2, the following macro will rename the sheets specified in your list, according to the names in your selection. Also, note that 'Sheets.Count' includes sheets other than worksheets, such as chart sheets. So you may want to use 'Worksheets.Count' instead, since it looks like you're only interested in renaming worksheets.

Code:
Option Explicit

Sub change_names()

    Dim cell1 As Range, cell2 As Range
    Dim s As Long, i As Long
    Dim thisWS As String
    
    s = Sheets.Count
    
    i = 1
    Set cell1 = Range("A2")
    For Each cell2 In Selection
        If i > s Then Exit Sub
        thisWS = cell2.Value
        Sheets(cell1.Value).Name = thisWS
        Set cell1 = cell1.Offset(1, 0)
        i = i + 1
    Next cell2
    
End Sub
 
Upvote 0
What sheet would you want the renaming to start on?
 
Upvote 0
Do you have a list of the Sheets you don't want to rename? That might be easier if the number of sheets to rename changes.

Which ever list never changes is the list to use. Excludes or Includes.

What is happening that you often have to rename a batch of worksheets?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top