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