Pasting an Array Formula Over a Copied Cell

Microsoft Excel 95 allowed you to paste an array formula over the copied cell. Microsoft Excel 97 does not. Whereas the following code worked in Microsoft Excel 95, it will not in Microsoft Excel 97:

Sub CopyAndPaste()
    With ActiveSheet
        .Range("A1").Copy
        .Range("A1:J1").Select
        .Paste
    End With
End Sub

To work around this you must not include the copied cell in the selection to paste to, or to use AutoFill, as in the following example:

Sub UseAutoFill()
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:J1"), Type:= _
        xlFillDefault
End Sub