Worksheet macro to copy formats and formula from discrete ranges

[jamie.cutting]

Could anyone suggest why the following doesnt work. Im using it behind
a worksheet to copy down formula but when I ammended it to include
copying from A85 to column A it stopped working. It also appears not
to copy formatting which I would like it to do. Could anyone make any
suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Columns(2), Target.EntireColumn) Is Nothing Then
Range("A85,AH12:CT12").Copy Intersect(Target.EntireRow,
Range("A,AH:CT"))
End If
End Sub

Regards

JAmie

 

[Guest]

Jamie,

I don't think you can copy cells from different rows at the same time...
maybe try it this way:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Columns(2), Target.EntireColumn) Is Nothing Then
Application.EnableEvents = False
Range("A85").Copy Target.Offset(0, -1)
Range("AH12:CT12").Copy Range("AH" & Target.Row)
Application.EnableEvents = True
End If
End Sub