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.
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.
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':
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
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
Is this what you're after:
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
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.
QuoteIf 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
Don’t have an account yet? Register yourself now and be a part of our community!