Excel AutoFillOption

[swtupr]

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???

 

[Jim Cone]

If you hold down the Ctrl key (while dragging) the number won't increment.
--
Jim Cone
Portland, Oregon USA


"swtupr"
<[email protected]>
wrote in message
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???

 

[swtupr]

Hi Jim,
Thanks for your reply.
Is there a way to do this through code with out holding the control key when
we drag directly?

 

[Jim Cone]

I don't know and haven't enough interest to try to find out.
You could also copy and paste.
Another approach would be to change Product1 to Product1_
--
Jim Cone
Portland, Oregon USA



"swtupr"
wrote in message
Hi Jim,
Thanks for your reply.
Is there a way to do this through code with out holding the control key when
we drag directly?