Rename sheets

Tom.Jones · Dec 25th 2017

Hello,

Can someone help me with a VBA code that will renames my selected sheets, depending on a list in a given sheet?
The sheets may be continuous or not.

Thanks in advance for any help.

**Trebor76** · Dec 26th 2017

Hi Tom.Jones,

Try this where the sheets that have been selected prior to running the macro will be renamed to what's in A2:A(last row in Col. A) of 'Sheet2':

Code

Option Explicit
Sub Macro1()


    Dim wsMySheet    As Worksheet
    Dim rngMyCell    As Range
    Dim i As Long, j As Long


    Application.ScreenUpdating = False


    For Each rngMyCell In Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)  'Works from cell A2 in 'Sheet2' down. Change to suit if necessary.
        i = i + 1
        For Each wsMySheet In ThisWorkbook.Windows(1).SelectedSheets 'https://msdn.microsoft.com/en-us/library/office/aa196884(v=office.11).aspx
            j = j + 1
            If i = j Then
                wsMySheet.Name = rngMyCell.Value
                j = 0 'Initialise variable
                Exit For
            End If
        Next wsMySheet
    Next rngMyCell


    Application.ScreenUpdating = True


End Sub

Display More

Note there's no check if the proposed name is valid or it already exists in the workbook.

Robert

Tom.Jones · Dec 26th 2017

Thank you very much, Trebor76.
Code VBA works well but I need to exclude sheet where run that code.
Thank you.

I did not manage to exclude the sheet from where I run the VBA code

**Trebor76** · Dec 26th 2017

Is this what you're after:

Code

Option Explicit
Sub Macro1()


    Dim wsMySheet    As Worksheet
    Dim wsExcludeTab As Worksheet
    Dim rngMyCell    As Range
    Dim i As Long, j As Long


    Application.ScreenUpdating = False


    Set wsExcludeTab = ActiveSheet


    For Each rngMyCell In Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)  'Works from cell A2 in 'Sheet2' down. Change to suit if necessary.
        i = i + 1
        For Each wsMySheet In ThisWorkbook.Windows(1).SelectedSheets 'https://msdn.microsoft.com/en-us/library/office/aa196884(v=office.11).aspx
            j = j + 1
            If i = j Then
                If wsMySheet.Name <> wsExcludeTab.Name Then
                    wsMySheet.Name = rngMyCell.Value
                    Exit For
                End If
            End If
        Next wsMySheet
        j = 0 'Initialise variable
    Next rngMyCell


    Application.ScreenUpdating = True


End Sub

Display More

Robert

Tom.Jones · Dec 26th 2017

Robert, BIG THANKS

If I select 4 sheets, the second VBA code rename only first 3 sheets
I manage to make a small change in your code and now is OK. Thank you for helping me.

**Trebor76** · Dec 26th 2017

Quote

If I select 4 sheets, the second VBA code rename only first 3 sheets

Correct - it won't rename the sheet you're on when you run the code as I thought that was what you were after. If you've sorted it now that's great.

Robert

Rename sheets

Participate now!

Share