Suppose that you have a workbook that contains lists on a large number of worksheets and that you want to change the name of each worksheet to match the heading of the list on that worksheet. Not every worksheet has a list on it, but if it does, the heading is in cell B1, and if it does not, cell B1 is blank. The names of worksheets without lists should be left alone.
Ordinarily, this could be a complex task that involves looking at each worksheet to see if it has a list, copying the name if it does, clicking the worksheet tab, and then pasting in the new name. Instead of performing all of those steps manually, use Excel VBA to rename the sheets automatically.
Learning about Objects
The first step is to find out how to manipulate the particular objects that you need to work with to accomplish your task; for example, worksheets, worksheet names, cells, and cell contents. In Excel, there are at least two ways to approach the problem:
- Go directly to the Object Model Reference.
- Record some of the actions that you want to automate, see how the recorded code manipulates the objects, and then go to the Object Model Reference for more information.
Opinions vary on which approach is preferable, but for now, try using the Macro Recorder first.
Using the Macro Recorder
Sometimes a simple recorded macro is all you need; in these cases, you do not even have to look at the code. More often, recording alone is not enough. Instead, it is a starting point in the following process.
To use the Macro Recorder as a starting point to your solution
- Record the actions that you want to code.
- Review the code and find the lines that perform those actions.
- Delete the rest of the code.
- Modify the recorded code.
- Add variables, control structures, and other code that the Macro Recorder cannot record.
Begin your investigation by recording a macro that renames a worksheet to New Name. You can then use the recorded macro to develop your own macro that renames multiple worksheets based on their contents.
To record a macro that renames a worksheet
- Click Record Macro on the Developer tab.
- Name the macro RenameWorksheets, rename Sheet1 to New Name, and click Stop Recording.
- Go to the Developer or View tab, click the Macros button, and choose Edit to open the Visual Basic Editor.
The code in the Visual Basic Editor should look similar to the following.
Sub RenameWorksheets()
’
’ RenameWorksheets Macro
’
’
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "New Name"
End Sub
The first four lines after the Sub line are comments. Any line that begins with an apostrophe is a comment and has no effect on what the macro does. The main uses for comments are the following:
- To make the code easier to understand, not just for you, but for anyone else who might need to modify the code later.
- To temporarily disable a line of code (called commenting it out).
The four comments in this recorded macro serve neither purpose, so delete them.
The next line uses the Select method to select the Sheet1 member of the Sheets collection object. In VBA code, it is not generally necessary to select objects before manipulating them, even though that is what the Macro Recorder does. In other words, this line of code is redundant, so you can delete it as well.
The last line of the recorded macro modifies the Name Property of the Sheet1 member of the Sheets collection. This is the line to keep.
After you make your changes, the recorded code should now look like the following.
Sub RenameWorksheets()
Sheets("Sheet1").Name = "New Name"
End Sub
Manually change the sheet called New Name back to Sheet1, then run the macro. The name should change back to New Name.
Modifying the Recorded Code
Now it is time to research the Sheets collection that the Macro Recorder used. The Sheets topic in the Object Model Reference includes the following text.
“The Sheets collection can contain Chart or Worksheet objects. If you need to work with sheets of only one type, see the object topic for that sheet type.”
You are only working with Worksheets, so change the code to the following.
Sub RenameWorksheets()
Worksheets("Sheet1").Name = "New Name"
End Sub
Looping
VBA has a construction called a For Each loop that is ideal. The For Each loop examines each item in a collection object such as Worksheets and can be used to take an action (like change a name) to some or all of those items.
For more information about the For Each loop, see the VBA Language Reference. Click “Visual Basic Conceptual Topics”, then “Using For Each…Next Statements”. Also, be aware that the VBA Language Reference, like the Object Model Reference, will amply repay the time that you spend browsing it, and is an excellent place to look for ideas if you get stuck working on code.
Using the third example in the “Using For Each…Next Statements” topic, edit the macro so that it looks similar to the following code.
Sub RenameWorksheets()
For Each myWorksheet In Worksheets
myWorksheet.Name = "New Name"
Next
End Sub
myWorksheet is a variable; that is, what it represents varies. In this case, the myWorksheet variable successively represents each worksheet in the Worksheets collection. You do not have to use myWorksheet; you could use “x”, “ws”, “WorksheetToRenameAfterTheContentsOfCellB1”, or (with a few restrictions) almost any name that you want. A good rule of thumb is to use variable names that are long enough to remind you of what the variable stands for, but not so long as to clutter the code.
If you run the macro in its current state, it produces an error because Excel requires each worksheet in a workbook to have a unique name, but the following line instructs Excel to give every worksheet the same name.
myWorksheet.Name = "New Name"
To correct the line so that you can verify that the For Each loop works, change the line to the following.
myWorksheet.Name = myWorksheet.Name & "-changed"
Instead of trying to give each worksheet the same name, this line changes the current name of each worksheet (myWorksheet.Name) to the current name with “-changed” appended to it.
Useful Renaming
The macro is getting close to something that might actually solve the problem at hand. What you need now is a way to take information from the worksheets themselves—specifically from cell B1 on each worksheet—and put that information into the names of the worksheets.
This time, instead of using the Macro Recorder to find out how to refer to a cell, take a guess and see if using the Cell object will work. It is a good guess, but if you open the Object Model Reference and search for the Cell object, you find that there is no Cell object! There is a CellFormat object though.
The CellFormat object topic includes the following code in the first code sample.
’ Set the interior of cell A1 to yellow.
Range("A1").Select
It turns out that you use Range to specify a range of cells or just one individual cell. Again, you do not need the .Select portion, but you do need to find out how to refer to the contents of the Range object, as opposed to the Range object itself. If you go to the Range object topic, you can read that Range has both Methods and Properties. The contents of a Range is a thing, not an action, so it would probably be a Property. If you scan down through the list, you can see the Value property. So, try the following.
Sub RenameWorksheets()
For Each myWorksheet In Worksheets
myWorksheet.Name = myWorksheet.Range("B1").Value
Next
End Sub
You get an error if you run this on a workbook that contains worksheets where B1 is empty, because an empty Range has a Value of “” (an empty text string), which is not a legal worksheet name. It is about time to create some sample data anyway. Make the three sheets in the workbook look similar to the figure below, and then run the macro.
Source: http://msdn.microsoft.com
