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

  1. Sub rename_multiple_tabs()
  2. Dim ws As Worksheet
  3. Dim ws1 As Worksheet
  4. Dim strErr As String
  5. Dim i as Integer
  6. On Error Resume Next
  7. i = 200 'this is where the index starts
  8. For Each ws In ActiveWorkbook.Sheets
  9. Set ws1 = Sheets("MySheet" & i)
  10. If ws1 Is Nothing Then
  11. ws.Name = "MySheet" & i
  12. Else
  13. strErr = strErr & ws.Name & i & vbNewLine
  14. End If
  15. Set ws1 = Nothing
  16. i = i + 1
  17. Next
  18. On Error GoTo 0
  19. If Len(strErr) > 0 Then MsgBox strErr, vbOKOnly, "these sheets already existed"
  20. 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