Hi,
I have a cell in excel sheet which has both text and Numbers as "Product1".
I want to copy this cell value to the next 10 cells and i have dragged the
auto fill. But the result is it keeps incrementing the number for every next
cell and gives me values product1,product2,product3,...... But i want to copy
Product1 in all cells. I know we can change the option to Copy cells in
autofill but i want to do this through VBA code.
Whenever we drag the autofill, the fill should just copy cells instead of
fill series. How can i do this using VBA?
I have tried the following:

Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Call Drag
End If
End Sub

Module:
Sub Drag()
ActiveCell.AutoFill Destination:=Range(Selection.Address),
Type:=xlFillCopy
End Sub

Its working fine if i want to copy for more than 1 cell. But if i drag just
for the cell below, it doesnt work. It increments again. I tried changing
code to

Target.Cells.Count = 1

But i get an error at line

ActiveCell.AutoFill Destination:=Range(Selection.Address), Type:=xlFillCopy

ANy help on this please???