Ms Office Tune Up

One of Excel’s unique features is that it “remembers” where you left off in a workbook when you last closed it, and it then restores that position the next time you open the work­book. For example, if the active cell is D5 in Sheet3 when you close a workbook, the next time you open that file, Excel automatically activates Sheet3 and selects cell D5. Not only that, but Excel also remembers the most recently used cell in every worksheet. This also applies to ranges: If you have ranges selected on any worksheet when you close the file, Excel reselects those ranges the next time you open the workbook.

This is useful because in most cases, it enables you to continue working from the spot where you stopped earlier when you closed the file. One situation where this is not great behavior is when you distribute a workbook to other people. When the other users open the file, Excel might take them to a cell or range in a worksheet that’s buried deep in the workbook. It’s more likely that you’ll want the workbook to have no selected ranges and to have the first worksheet activated. A good way to do this is to select cell A1 on each work­sheet and to finish by activating the first worksheet. This is reasonable in a workbook with just a few sheets, but it’s a hassle in large workbooks. To eliminate that hassle, Listing 6.4 presents a VBA procedure that selects cell A1 in all of a workbook’s sheets.

Listing 6.4 A VBA Procedure That Selects Cell A1 on All the Sheets in the Active Workbook

The procedure runs through all the worksheets in the active workbook. In each case, the worksheet is first activated (you must activate a sheet before you can select anything on it) and then the Select method is called to select cell A1. The macro finishes by activating the first worksheet.

Technorati Tags: selecting sheets, worksheets, excel 2007

Popularity: 3% [?]