Page 188 - Microsoft Office Excel 2003 Programming Inside Out

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

Selecting a Range
The Select method of a Range object provides various ways to select a range of cells. Many of
the procedures are similar to selecting cells using the keyboard, making it very easy to emu-
late the way you would work if you were actually typing in the keystrokes needed to select
the cells.
The following example uses the CurrentRegion property of the ActiveCell object to select the
range of cells that are currently being used within the worksheet. The range is copied to the
clipboard, pasted onto a new worksheet, and then necessary formatting is applied and the
value contents are erased, leaving blank cells for a new year’s worth of information in the
SalesByCategory.xls workbook.

Chapter 8

Sub InsertNewSheet()

Range("C1").Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "New Year"
Sheets("New Year").Select
Range("C1").Activate
ActiveSheet.Paste
Columns("C:H").EntireColumn.AutoFit
Range("D2:G13").Select
Selection.ClearContents

End Sub

162