Rename worksheets macro needed

[Harry Flashman]

I would like a macro which allows me to rename existing worksheets
according to the values in a table (located on a table of contents
worksheet). The existing worksheet names might be contained (for
example) in A1:A10 and the new names in B1:B10.
I have some macro's already that perform similar tasks:

I have a macro which allows me to create worksheets based on a table
(for example cells A1:A10), which creates the worksheets and orders
the worksheets as per the table,
I also have a macro, which allows me to sort the worksheet according
to the table. This means I can change the order in the table (which
contains the worksheet names), and then sort the worksheets
accordingly.
Here are the macros: (the second macro is the one I think can be
altered to do what I want).

Sub NewWorkSheets()
' before this macro is run make sure the first worksheet is named
"Table of Contents"
' this macro will insert new worksheets in the correct order
On Error Resume Next
Dim Arr As Variant
Dim i As Long
Dim NewSheet As Worksheet
Arr = Selection.Value
For i = LBound(Arr) To UBound(Arr)
Set NewSheet = Sheets.Add
NewSheet.Name = Arr(i, 1)
Next i
Sheets("Table of Contents").Select
Sheets("Table of Contents").Move Before:=Sheets(1)
'Sort worksheets based on table on worksheet 1
On Error Resume Next
Dim SortOrder As Variant
Dim Ndx As Long
With Selection
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move after:=Worksheets(1)
Next Ndx
End With
Sheets("Table of Contents").Select

End Sub

The second macro is actually contained in the first but I have this
version in case I just want to sort the worksheets and not create new
ones.

Sub SortWorksheets()
'Sort worksheets based on table on worksheet 1
On Error Resume Next
Dim SortOrder As Variant
Dim Ndx As Long
With Selection
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move after:=Worksheets(1)
Next Ndx
End With
Sheets("Table of Contents").Select
End Sub

The above macro is clever enough to recognize the worksheet names from
the table, and then move the worksheet. I was hoping it might have the
potential (with some modification) to rename the worksheet based on
the table. For example the worksheet names might be contained in cells
A1:A10, and the new names in B1:B10.
Alas I know very little about VBA. If anyone could help me I would
appreciate it.

Harry

 

[David Heaton]

I would like a macro which allows me to rename existing worksheets
according to the values in a table (located on a table of contents
worksheet). The existing worksheet names might be contained (for
example) in A1:A10 and the new names in B1:B10.
I have some macro's already that perform similar tasks:

I have a macro which allows me to create worksheets based on a table
(for example cells A1:A10), which creates the worksheets and orders
the worksheets as per the table,
I also have a macro, which allows me to sort the worksheet according
to the table. This means I can change the order in the table (which
contains the worksheet names), and then sort the worksheets
accordingly.
Here are the macros: (the second macro is the one I think can be
altered to do what I want).

Sub NewWorkSheets()
' before this macro is run make sure the first worksheet is named
"Table of Contents"
' this macro will insert new worksheets in the correct order
On Error Resume Next
    Dim Arr As Variant
    Dim i As Long
    Dim NewSheet As Worksheet
    Arr = Selection.Value
    For i = LBound(Arr) To UBound(Arr)
        Set NewSheet = Sheets.Add
        NewSheet.Name = Arr(i, 1)
    Next i
    Sheets("Table of Contents").Select
    Sheets("Table of Contents").Move Before:=Sheets(1)
    'Sort worksheets based on table on worksheet 1
On Error Resume Next
Dim SortOrder As Variant
Dim Ndx As Long
With Selection
    For Ndx = .Cells.Count To 1 Step -1
        Worksheets(.Cells(Ndx).Value).Move after:=Worksheets(1)
    Next Ndx
End With
Sheets("Table of Contents").Select

End Sub

The second macro is actually contained in the first but I have this
version in case I just want to sort the worksheets and not create new
ones.

Sub SortWorksheets()
'Sort worksheets based on table on worksheet 1
On Error Resume Next
Dim SortOrder As Variant
Dim Ndx As Long
With Selection
    For Ndx = .Cells.Count To 1 Step -1
        Worksheets(.Cells(Ndx).Value).Move after:=Worksheets(1)
    Next Ndx
End With
Sheets("Table of Contents").Select
End Sub

The above macro is clever enough to recognize the worksheet names from
the table, and then move the worksheet. I was hoping it might have the
potential (with some modification) to rename the worksheet based on
the table. For example the worksheet names might be contained in cells
A1:A10, and the new names in B1:B10.
Alas I know very little about VBA. If anyone could help me I would
appreciate it.

Harry

Harry,

This is a bit crude but will do the job for you


On Error Resume Next
Dim SortOrder As Variant
Dim Ndx As Long
Dim i As Integer
With Selection
For Ndx = .Cells.Count To 1 Step -1
For i = 0 To Worksheets.Count
If Worksheets(i).Name = .Cells(Ndx).Value Then
Worksheets(Ndx).Name = .Cells(Ndx, 2)
Exit For
End If
Next
Next Ndx
End With


It runs through the sheet names in your selection and if it finds it
changes the name to whatever is in the next column.


It should get you started


Regards


David

 

[Harry Flashman]

Harry,

This is a bit crude but will do the job for you

On Error Resume Next
Dim SortOrder As Variant
Dim Ndx As Long
Dim i As Integer
With Selection
    For Ndx = .Cells.Count To 1 Step -1
        For i = 0 To Worksheets.Count
            If Worksheets(i).Name = .Cells(Ndx).Value Then
                Worksheets(Ndx).Name = .Cells(Ndx, 2)
                Exit For
            End If
        Next
    Next Ndx
End With

It runs through the sheet names in your selection and if it finds it
changes the name to whatever is in the next column.

It should get you started

Regards

David- Hide quoted text -

- Show quoted text -

Thanks David. That almost does what I want and I'll shall study it to
help me learn about VBA.
Presently it changes the first worksheet in the book to the first name
in the selection. In my workbooks the first worksheet is called "Table
of Contents".

For example if my worksheet names are in column A and the new names
are in column B...
A B
Test1 TestA
Test2 TestB
Test3 TestC
Test4 TestD
Test5 TestE
Test6 TestF
Test7 TestG
Test8 TestH
Test9 TestI

Then the Table of Contents - which is before Test1, gets renamed
TestA, and Test1 gets renamed TestB etc, whereas I want Test1 to be
renamed TestA.
Ideally I want this macro to have the capability to change the name of
only some of the worksheets and not necessarily all the worksheets in
the workbook: that is only those worksheets whose name is found in the
selection.

Your effort is an excellent start though and I'll study it.

Cheers,
Harry

 

[Harry Flashman]

Harry,

This is a bit crude but will do the job for you

On Error Resume Next
Dim SortOrder As Variant
Dim Ndx As Long
Dim i As Integer
With Selection
    For Ndx = .Cells.Count To 1 Step -1
        For i = 0 To Worksheets.Count
            If Worksheets(i).Name = .Cells(Ndx).Value Then
                Worksheets(Ndx).Name = .Cells(Ndx, 2)
                Exit For
            End If
        Next
    Next Ndx
End With

It runs through the sheet names in your selection and if it finds it
changes the name to whatever is in the next column.

It should get you started

Regards

David- Hide quoted text -

- Show quoted text -

I have taken another look. Your macro works brilliantly. I just need
to include the Table of Contents sheet in my table.
Thank you very much. I am extremely grateful.

Cheers