How to Use a Macro to Change the Name of a Sheet in Excel

Using an Excel macro, you can do almost anything that you can do manually. Although using a macro to change a sheet name in Excel can be daunting if you've never written a macro, once you have the mechanics of creating a macro down, the actual code is pretty straightforward.

Instructions

1. Open the Visual Basic Editor from the Tools Macro menu or pressing Alt-F11.

2. Select Module from the Insert menu.

3. Type the following code into the module:

   Sub RenameSheet(oldSheetName, newSheetName)
   Sheets(oldSheetName).Name = newSheetName
   End Sub

4. Add some test code to make sure the macro works:

   Sub TestRenameSheet()
   RenameSheet "sheet1", "A New Sheet Name"
   End Sub

5. Go back to the Excel worksheet and run the RenameSheet macro. Select Macros from the Tools Macro menu. In the Macro dialog, select RenameSheet and click the Run button. Sheet1 should now be renamed to A New Sheet Name.

Tips & Warnings

• Recording a macro is a quick way to discover macro steps for Excel. Select Record New Macro from the Tools Macro menu, then do what you want your macro to do. Excel will record your actions until you click on the square button in the macro toolbar. Then you can edit the macro to see what the steps look like. This doesn't always give you exactly what you want, but it's a handy way to start.

• Be careful about hard-coding sheet names. Even the default names may be different in different language versions.