Macro To Insert Worksheet

June 18, 2002 - by Bill Jelen

"Stuck in NJ" asked this week's question:

I am creating an Excel workbook that contains charts of sales figures. The first Excel worksheet corresponds to the first sales territory in the company (eg. FL01.) The worksheet's tab name (as well as cell A1) is the sales territory number FL01. The data that drives the charts is to the right (outside of the printable range that I set) and is populated from another excel workbook using VLOOKUP with the sales territory number FL01 in cell A1 as the lookup key. I need to add to the workbook 76 identical worksheets (one for each sales territory) such that each sheet's tab name and cell A1 equals successive sales territory numbers (eg. FL01, FL02, LK01, LK02.) How do I do that in an automated way?

This is easily accomplished with a VBA macro, but let me cover the tip for doing this manually. Once you have a sheet set up with formatting and print ranges, you can easily duplicate the sheet by right-clicking on the tab for that sheet. From the menu that pops up, choose Move or Copy. From the Move or Copy dialog box, select "(move to end)" and check the "Create a Copy" box, then click OK. A copy of your sheet with formatting will be added as a new worksheet in your workbook. (To speed up this process, hold down the Ctrl key while dragging the worksheet tab to the right.) You will usually want to rename the sheet. Right-click the new tab, choose rename, and type a meaningful name.

For the Excel macro to work, it needs a list of territories. Before you write the macro, insert a new sheet in this book named Data. In Cell A1, enter the 2nd sales territory (FL02 in your example). Continue entering all of the sales territories down column A. Do not leave any blank rows.

Insert a new macro and copy this code:

Public Sub CopyIt()
    Sheets("Data").Select
    ' Determine how many territories are on Data sheet
    FinalRow = Range("A65000").End(xlUp).Row
    ' Loop through each territory on the data sheet
    For x = 1 To FinalRow
        LastSheet = Sheets.Count
        Sheets("Data").Select
        ThisTerr = Range("A" & x).Value
	' Make a copy of Fl01 and move to end
        Sheets("FL01").Copy After:=Sheets(LastSheet)
	' rename the sheet and set A1 = to the territory name
        Sheets(LastSheet + 1).Name = ThisTerr
        Sheets(ThisTerr).Select
        Range("A1").Value = ThisTerr
    Next x
End Sub

The macro makes use of the Sheets().copy command which does the same thing as clicking Move or Copy manually. Before doing each copy, I find out the number of sheets by using the Sheets.Count property. I know if there are 56 sheets and I insert a new sheet, it will be referenced as Sheets(57). I find it somewhat unpredictable to guess how Excel will name the new sheet, so I use numeric index numbers to identify the sheet. Once I rename the new sheet with "Sheets(LastSheet + 1).Name = ThisTerr", I can then switch back to my preferred method of referring to the sheet by using its name.

Note that the list of territories should not contain FL01 in the list. Trying to rename a copy a copy of FL01 with the same name will cause an error in the macro.