Ms Office Tune Up

Early versions of Excel used to give you 16 worksheets by default in each new workbook. Somebody at Microsoft must have realized this was an absurdly large number, so recent versions of Excel populate new workbooks with just three worksheets. That’s a reasonable number because most people probably just use a single worksheet in most of their work­books. However, it’s not uncommon to have workbooks with 10 or 20 sheets, depending on the application. Rather than adding the extra sheets by hand, Listing 1 presents a VBA procedure that prompts you for the number of sheets you want and then creates a new workbook populated with that many sheets.

TIP

You can change the number of worksheets that Excel adds by default to new workbooks. Choose Office, Excel Options to display the Excel Options dialog box. Click Popular and then use the Include This Many Sheets spin box to set the number of worksheets you want in each new workbook. Click OK to put the new setting into effect.

Listing 1 A VBA Procedure That Prompts You to Specify the Number of Sheets You Want in a
New Workbook

The value of the Include This Many Sheets setting is given by the Application object’s SheetsInNewWorkbook property. The procedure first stores the current SheetsInNewWorkbook value in the currentSheets variable. Then the macro runs the InputBox function to get the number of required sheets (with a default value of 3), and this value is assigned to the SheetsInNewWorkbook property. Then the Workbooks.Add statement creates a new workbook (which has the specified number of sheets) and the SheetsInNewWorkbook property is returned to its original value.

Technorati Tags: workbook specified number, number sheets, excel 2007

Popularity: 2% [?]