Rename multiple Excel worksheets

How to rename multiple Excel worksheets at the same time using VBA

METHOD 1. Rename multiple Excel worksheets using VBA

VBA

Sub Rename_Multiple_Excel_Worksheets()

'declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

'rename three worksheets at the same time
ws1.Name = "Data"
ws2.Name = "Analysis"
ws3.Name = "Summary"

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

PREREQUISITES
Minimum Number of Worksheets: The workbook has at least three worksheets.
Worksheet Names: Three of the worksheets are named Sheet1, Sheet2 and Sheet3.

ADJUSTABLE PARAMETERS
Worksheets to Rename: Select worksheets that you want to rename by changing Sheet1, Sheet2 and Sheet3 worksheet names in the VBA code to any worksheet in the workbook.
Rename Worksheets: Rename worksheets to any name, within Excel limits, by changing the Data, Analysis and Summary worksheet names in the VBA code.

METHOD 2. Rename multiple Excel sheets from a list of names that you want to rename the sheets to using VBA

VBA

Sub Rename_Multiple_Excel_Worksheets()

'declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

'set the cell references that contain the new worksheet name
Set wsName= Worksheets("Parameters").Range("C2")
Set wsName= Worksheets("Parameters").Range("C3")
Set wsName= Worksheets("Parameters").Range("C4")

'rename three worksheets at the same time
ws1.Name = wsName1.Value
ws2.Name = wsName3.Value
ws2.Name = wsName3.Value

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Minimum Number of Worksheets: Have at least four worksheets, including a Parameters worksheet.
Worksheet Names: Have four worksheets named Parameters, Sheet1, Sheet2 and Sheet3.
Worksheets to Rename: Cells ("C2"), ("C3") and ("C4") in the Parameters worksheet need to capture the names that you want to rename the worksheets to.

ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet that capture the new worksheet names by changing the Parameters worksheet name in the VBA code.
Worksheets to Rename: Select the worksheets that you want to rename by changing the Sheet1, Sheet2 and Sheet3 worksheet names in the VBA code to any worksheet in the workbook. These names can also be referenced to through specific cells that contain the names of the worksheets to be renamed.
Rename Worksheets: Rename worksheets to any name, within Excel limits, by changing the names in cells ("C2"), ("C3") and ("C4") in the Parameters worksheet.

METHOD 3. Rename multiple Excel worksheets from a list of names of both worksheets that you want to rename and what you want to rename them to using VBA

VBA

Sub Rename_Multiple_Excel_Workheets()

On Error Resume Next

For Each oldwsnames In ThisWorkbook.Worksheets("Parameters").Range("D2:D4")
ThisWorkbook.Worksheets(oldwsnames.Value).Name = oldwsnames.Offset(0, -1).Value

End Sub

PREREQUISITES
Minimum Number of Worksheets: Have at least four worksheets, including a Parameters worksheet.
Worksheets to Rename: Cells ("C2"), ("C3") and ("C4") in the Parameters worksheet need to capture the names that you want to rename the worksheets to.
Rename Worksheets: Cells ("D2"), ("D3") and ("D4") in the Parameters worksheet need to capture the names of the worksheets you want to rename.

ADJUSTABLE PARAMETERS
Worksheets to Rename: Select the worksheets that you want to rename by changing the names in cells ("C2"), ("C3") and ("C4") in the Parameters worksheet.
Rename Worksheet: Rename worksheets to any name, within Excel limits, by changing the names in cells ("D2"), ("D3") and ("D4") in the Parameters worksheet.
Worksheet Selection: Select the worksheet that capture the new worksheet names and the worksheets that you want to rename by changing the Parameters worksheet name in the VBA code.

METHOD 4. Rename multiple Excel sheets from a list with a For Loop using VBA

VBA

Sub Rename_Multiple_Excel_Worksheets()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Parameters")

For x = 2 To 4

On Error Resume Next

oldwsnames = ws.Cells(x, 4).Value
newwsnames = ws.Cells(x, 3).Value
Worksheets(oldwsnames).Name = newwsnames

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

PREREQUISITES
Minimum Number of Worksheets: Have at least four existing worksheets, including the Parameters worksheet.
Worksheets to Rename: Cells ("C2"), ("C3") and ("C4") in the Parameters worksheet need to capture the names that you want to rename the worksheets.
Rename Worksheets: Cells ("D2"), ("D3") and ("D4") in the Parameters worksheet need to capture the names of the worksheets you want to rename.

ADJUSTABLE PARAMETERS
Worksheets to Rename: Select the worksheets that you want to rename by changing the names in cells ("D2"), ("D3") and ("D4") in the Parameters worksheet.
Rename Worksheets: Rename worksheets to any name, within Excel limits, by changing the names in cells ("C2"), ("C3") and ("C4") in the Parameters worksheet.
Worksheet Selection: Select the worksheet that capture the new worksheet names and the worksheets that you want to rename by changing the Parameters worksheet name in the VBA code.

Explanation about how to rename multiple worksheets

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to rename multiple worksheets using VBA.

VBA Methods: Using VBA you can rename multiple worksheets by directly entering the names of the worksheets that you want to rename and rename to or by referencing to a range of cells that hold the names of the worksheets that you want to rename and rename to.