Microsoft Office Tutorials and References
In Depth Information
CREATE WORKSHEET NAMES BY USING THE FILL HANDLE
‘ Go back to the original worksheet
Application.ScreenUpdating = True
This macro creates a temporary workbook. It types the sheet name into cell A1
of the temporary workbook and then ﬁ lls the series. A loop then starts copying
the original worksheet and uses names from the ﬁ lled 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.
Figure 134. Excel asks how many sheets to copy.
The macro works well for months, day names, and quarters. It even works for
numbers, a slight improvement on the ﬁ ll handle because you need to hold
down the Ctrl key to coax 1 to ﬁ ll 1, 2, 3. However, the macro has problems
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
ﬁ lled series contains slashes, which are invalid worksheet names.
Even more frustrating, the logic to ﬁ gure out the custom number format to
replicate the original date is difﬁ cult. You can’t learn the correct format from