Autocopy formula to cells

[Marie Bayes]

I need a VB expert's help I think. I want formulas from several columns to
automatically copy down from the cells above, but only once data has been
input to another cell within the row. ie. once the cell A12 has data in then
the formulas from cells E11, F11, G11, H11, I11, J11 are copied into the
corresponding cells in row 12. Can anybody help?

[Stefi]

Hi Marie,

Try to apply this Change event sub:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Range("E" & Target.Row - 1 & ":J" & Target.Row - 1).AutoFill
Destination:=Range("E" & Target.Row - 1 & ":J" & Target.Row),
Type:=xlFillDefault
End If
End Sub

Post if you need help to install it!

Regards,
Stefi



„Marie Bayes” ezt �*rta:

> I need a VB expert's help I think. I want formulas from several columns to
> automatically copy down from the cells above, but only once data has been
> input to another cell within the row. ie. once the cell A12 has data in then
> the formulas from cells E11, F11, G11, H11, I11, J11 are copied into the
> corresponding cells in row 12. Can anybody help?

[Don Guillett]

You can use a worksheet_change event for this. right click sheet tab>view
code>left window select worksheet>right window select change>

something like

tr=target.row
if target.column<>1 then exit sub
range(cells(tr-1,"e"),cells(tr-1,"j")).copy cells(tr,"e")

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Marie Bayes" <MarieBayes@discussions.microsoft.com> wrote in message
news:D992E9B0-CA2A-483F-8CA9-2B98770F4578@microsoft.com...
>I need a VB expert's help I think. I want formulas from several columns to
> automatically copy down from the cells above, but only once data has been
> input to another cell within the row. ie. once the cell A12 has data in
> then
> the formulas from cells E11, F11, G11, H11, I11, J11 are copied into the
> corresponding cells in row 12. Can anybody help?

[Marie Bayes]

Hi Don

That's almost what I require only it's now copying the contents of the cells
above and not just the formula. Is it possible just to copy the formula?

"Don Guillett" wrote:

> You can use a worksheet_change event for this. right click sheet tab>view
> code>left window select worksheet>right window select change>
>
> something like
>
> tr=target.row
> if target.column<>1 then exit sub
> range(cells(tr-1,"e"),cells(tr-1,"j")).copy cells(tr,"e")
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Marie Bayes" <MarieBayes@discussions.microsoft.com> wrote in message
> news:D992E9B0-CA2A-483F-8CA9-2B98770F4578@microsoft.com...
> >I need a VB expert's help I think. I want formulas from several columns to
> > automatically copy down from the cells above, but only once data has been
> > input to another cell within the row. ie. once the cell A12 has data in
> > then
> > the formulas from cells E11, F11, G11, H11, I11, J11 are copied into the
> > corresponding cells in row 12. Can anybody help?
>
>
>

[Don Guillett]

I assumed that the cells in question were formulas that referred to a,b,c &
d. If so, thats what you need.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Marie Bayes" <MarieBayes@discussions.microsoft.com> wrote in message
news:C296BA76-9B4C-4E16-89E7-28EC060303CA@microsoft.com...
> Hi Don
>
> That's almost what I require only it's now copying the contents of the
> cells
> above and not just the formula. Is it possible just to copy the formula?
>
> "Don Guillett" wrote:
>
>> You can use a worksheet_change event for this. right click sheet tab>view
>> code>left window select worksheet>right window select change>
>>
>> something like
>>
>> tr=target.row
>> if target.column<>1 then exit sub
>> range(cells(tr-1,"e"),cells(tr-1,"j")).copy cells(tr,"e")
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Marie Bayes" <MarieBayes@discussions.microsoft.com> wrote in message
>> news:D992E9B0-CA2A-483F-8CA9-2B98770F4578@microsoft.com...
>> >I need a VB expert's help I think. I want formulas from several columns
>> >to
>> > automatically copy down from the cells above, but only once data has
>> > been
>> > input to another cell within the row. ie. once the cell A12 has data in
>> > then
>> > the formulas from cells E11, F11, G11, H11, I11, J11 are copied into
>> > the
>> > corresponding cells in row 12. Can anybody help?
>>
>>
>>