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
 

kendel

Board Regular
Joined
Mar 2, 2010
Messages
133
is this a really hard problem or so easy nobody will bother?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,010
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
 

kendel

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

kendel

Board Regular
Joined
Mar 2, 2010
Messages
133
help plz
thanks
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,010
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
What sheet would you want the renaming to start on?
 

SamTYler

Well-known Member
Joined
Mar 10, 2004
Messages
784
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:

Forum statistics

Threads
1,082,109
Messages
5,363,193
Members
400,721
Latest member
eileen123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top