AutoFill Destination:=Range("E2:E4"), Type:=xlFillDefault
Range("E2:E4").Select
Range("E6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Range("E7").Select
If you select cell E2 and choose the Copy command, select the range E3:E4, and then
choose the Paste command, Excel will generate this code:
Range("C6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("E2").Select
'Used Copy command to copy formula to E3:E4
Selection.Copy
Range("E3:E4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Range("E7").Select
The code is identical up until the second Range("E2").Select command. In the first
example, the fill method of copying was used, and we see Excel??™s AutoFill method invoked.
The AutoFill method takes two arguments, the range to fill (including the source
range) and the type of fill to apply. The Type argument takes a value whose data type is
xlAutoFillType enumeration. These correspond to the Series dialog and can contain the
values listed in Table 1-2. These values can be combined by using the And operator (as in
xlFillSeries And xlFillFormats).
CHAPTER 1 n THE MACRO RECORDER AND CODE MODULES 18
Table 1-2. xlAutoFillType Enumerations
Name Value Description
xlFillCopy 1 Copies the values and formats from the source range to the target
range
xlFillDays 5 Extends the names of the days of the week in the source range into
the target range
xlFillDefault 0 Lets Excel determine the values and formats used to fill the target
range
xlFillFormats 3 Copies only the formats from the source range to the target range.
Pages:
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44