Create Worksheet Names By Using The Fill Handle

June 07, 2021 - by Bill Jelen

Challenge: You type Jan into a cell, grab the fill handle, and drag down five cells. Excel types Marcia, Cindy, Bobby, Greg, and Peter. No, sorry. Excel types Feb, Mar, Apr, May, Jun. The fill handle can do all sorts of amazing fills, handling months, quarters, weekdays, dates, and so on. If you set up a custom list, Excel can even extend your list of departments, products, or Brady Bunch kids. The fill handle is so useful, wouldn’t it be cool if you could use it to copy the current worksheet into new worksheets that have appropriate names? For example, if you have a worksheet named Tuesday and used the fill handle, you could have Excel add new worksheets Wednesday, Thursday, and Friday to the right of Tuesday.

Solution: Add the following code to your Personal Macro Workbook:

This macro creates a temporary workbook. It types the sheet name into cell A1 of the temporary workbook and then fills the series. A loop then starts copying the original worksheet and uses names from the filled series.

Press Alt+F8, choose the macro, choose Options, and assign the shortcut key Ctrl+Shift+F to the macro.

When you run the macro, Excel asks how many sheets you want to insert (Figure 134). Because my most common situation is copying Jan to the remaining 11 months, I used 11 as the default in the input box. Feel free to adjust it to your most common situation.

The macro works well for months, day names, and quarters. It even works for numbers, a slight improvement on the fill handle because you need to hold down the Ctrl key to coax 1 to fill 1, 2, 3. However, the macro has problems with dates.

The standard U.S. date format is 9/15/2009 for September 15, 2009. This is an illegal sheet name because you cannot include slashes in a worksheet name. So, although no one would have a worksheet name like 9/15/2009 to begin with, you might have worksheet names that spell out dates in another format. For example, 9-15, Sep 15, and 9-15-2009 are all valid worksheet names. Here is the problem: When the macro types those names into cell A1 of a temporary worksheet, Excel instantly converts the value to 9/15/2009. The filled series contains slashes, which are invalid worksheet names.

Even more frustrating, the logic to figure out the custom number format to replicate the original date is difficult. You can’t learn the correct format from Cells (1, 1) .NumberFormat because in most cases, typing the value causes Excel to change the number format.

As a compromise, the macro types the worksheet name preceded by an apostrophe. This allows 9-15 and Sep 15 entries to work through the end of the month. Note that these text entries work just like Room 101 works in the fill handle. Sep 15 accurately jumps to Sep 16, but Jan 31 inaccurately jumps to Jan 32! Figure 135 shows several examples of workbooks where the first worksheet was copied using the macro in this chapter.

Tip: If you define a custom list, you can have this macro use the names in the custom list.

Summary: Perhaps Microsoft will add this functionality to a future version of Excel. Until it does, use this macro to copy worksheets and change their names to a known series.

Title Photo: Matt Hoffman on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.