Page 138 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
Microsoft Office Excel 2003 Programming Inside Out

Figure 6-3. The ActiveChart property lets you streamline your chart creation and
manipulation.

ActiveSheet Property
The ActiveSheet property returns a worksheet object that represents the currently selected
worksheet. You can perform all worksheet manipulations using this object. For example, if
you created a workbook where each worksheet recorded hourly sales figures for every day in
a month, you could write a macro to create a new sheet with the name of the next month and
the correct weekday next to the date. Figure 6-4 shows the results of this macro, which uses
the January worksheet of the Y2004ByMonth.xls workbook as its model.

Sub CopySheet()

Sheets("January").Select 'Select sheet to copy
ActiveSheet.Copy After:=Sheets(Sheets.Count) 'Copy sheet to last position
Sheets(Sheets.Count).Select 'Select new sheet
ActiveSheet.Name = Format(Str$(Sheets.Count) & "/01/03", "MMMM")
'Rename to latest month
Range("D6:O36").Select 'Select data cells and erase
Selection.ClearContents
Range("C6").Select 'Start at first day, set day of week for first 3 days
ActiveCell.FormulaR1C1 = Format(Str$(Sheets.Count) & "/01/" & _
Str$(Year(Now())), "ddd")
Range("C7").Select
ActiveCell.FormulaR1C1 = Format(Str$(Sheets.Count) & "/02/" & _
Str$(Year(Now())), "ddd")
Range("C8").Select
Chapter 6
ActiveCell.FormulaR1C1 = Format(Str$(Sheets.Count) & "/03/" & _

112