ExcelMadeEasy: Vba rename multiple sheets with vba in Excel

Vba rename multiple sheets with vba in Excel

You have multiple sheets and want to rename them.

For example you want to give them a specific name with an index

Here you will use the For Each Worsheet loop to browse through all the Worksheets and change their name

Sub rename_multiple_tabs()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim strErr As String
Dim i as Integer
On Error Resume Next
i = 200 'this is where the index starts
For Each ws In ActiveWorkbook.Sheets
Set ws1 = Sheets("MySheet" & i)
If ws1 Is Nothing Then
ws.Name = "MySheet" & i
Else
strErr = strErr & ws.Name & i & vbNewLine
End If
Set ws1 = Nothing
i = i + 1
Next
On Error GoTo 0
If Len(strErr) > 0 Then MsgBox strErr, vbOKOnly, "these sheets already existed"
End Sub

For example you want to rename it to "MySheet200", "MySheet201", �

This is the result of the renaming

excel vba rename multiple sheets with vba

You can find similar Excel Questions and Answer hereunder

1) Vlookup to return max value from multiple hits in Excel

2) How can I save a WorkSheet as a new WorkBook using VBA?

3) How can I prevent users from seeing / accessing my macro code?

4) How to change desktop background in Excel

5) How to copy files in Excel VBA is explained here

6) Vba delete entire row if contains certain text in Excel

7) Vba code to password protect workbook in Excel

8) How can I export a chart as a gif file?

9) How to read a value from a cell in vba in Excel

10) How can I set the fill color, font color and set number format of cell to date?

Here the previous and next chapter