Written by Allen Wyatt (last updated September 24, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Koen has a worksheet that has a list of names in column A. She needs to create a worksheet for each name in the list and have the worksheet named according to that name. Koen suspects this will require a macro, but she's not sure how to go about such a task.
This task is relatively easy to do if you use a macro, and there are any number of ways you could go about it. One simple way is to select your list of worksheet names and then run the following macro.
Sub AddWorksheetsFromSelection() Dim CurSheet As Worksheet Dim Source As Range Dim c As Range Set CurSheet = ActiveSheet Set Source = Selection.Cells Application.ScreenUpdating = False For Each c In Source sName = Trim(c.Text) If Len(sName) > 0 Then Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = sName End If Next c CurSheet.Activate Application.ScreenUpdating = True End Sub
The macro essentially grabs each cell in your selection, creates a new worksheet, and then renames that worksheet according to whatever was in the cell.
The macro checks to make sure that a particular cell actually contains something (you can't rename a worksheet if there is no name in the cell), but it still isn't nearly as robust as it might be. There could be other flaws in your list of worksheet names that might lead to errors when the macro is run. For instance, what if your list contains duplicates? Or it contains names that Excel doesn't allow? These (and any number of other errors) could be anticipated and the code changed to handle such situations.
While using a macro to create the worksheets is fast and easy, you may want to note that you don't necessarily need to use a macro. In fact, you could use the PivotTable capabilities of Excel to create the desired worksheets. Let's assume, for the sake of this example, that your desired worksheet names are in column A of a worksheet, and that cell A1 contains a heading for the column (such as "Names" or "Worksheets"). What you want to do is to create a PivotTable that is based on these names. Follow these steps:
Figure 1. The Create PivotTable dialog box.
Figure 2. The PivotTable Fields pane with a filter set.
It is important to realize that at this point each of the new worksheets contains a small PivotTable. To get rid of these PivotTables, you might think that you can create a selection set of the new worksheets (click the first worksheet tab and then hold down the Shift key as you click the last worksheet tab) and then press the Delete key. In my testing, though, this doesn't work—Excel won't let you make changes to PivotTables in group edit mode. Instead, you'll need to display each worksheet, in turn, and delete the PivotTables.
This may seem like a lot of work, but if you only need to create all these worksheets a single time, it can be a relatively quick way to do it without the need of invoking a macro.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13463) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides ...Discover More
Excel, by default, recalculates your worksheets as you make changes in those worksheets. If you want to limit the number ...Discover More
Ever want to use the name of a worksheet tab within a cell? Here's how you can access that information using the CELL ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.