Autocopy formula to cells

M

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

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:
 
D

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")
 
M

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

Don Guillett

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top