Copy a Cell with a macro

[Otter]

I am trying to copy a formula from Cell M18 to Cells M19 thru M58 if the
value in Cell F3 is "Y". I get a "Autofill Method of Range class failed"
error. Sometimes it works on column "M" but then gives me an
"Application-defined or object-defined error" error when it tries to do the
same thing in column "U". Here is the code CPY_FORMULA

Thank you in advance for your help.

Application.EnableEvents = False

If (Target.Column = 11 And (Target.Row = 2 Or Target.Row = 3 Or Target.Row =
4)) Then

Worksheets("Data").Unprotect PWORD
Call Analysis_Sales
Worksheets("Data").Unprotect PWORD

End If

If (Target.Column = 6 And Target.Row = 3) Then

Worksheets("Analysis").Unprotect PWORD
Range("F3").Value = UCase(Range("F3").Value)
If Range("F3") <> "Y" Then
Range("F3") = "N"
End If

Call Cpy_Formula

Worksheets("Analysis").Protect PWORD

End If


Application.EnableEvents = True


End Sub

Private Sub Cpy_Formula()


If Range("F3").Value = "Y" Then

Range("M19:M58").Interior.ColorIndex = 4
Selection.AutoFill Destination:=Range("M18:M58"), Type:=xlFillDefault
Range("M18:M58").Select

Range("U19:U58").Interior.ColorIndex = 8
Selection.AutoFill Destination:=Range("U18:U58"), Type:=xlFillDefault
Range("U18:U58").Select

Range("AC19:AC58").Interior.ColorIndex = 39
Selection.AutoFill Destination:=Range("AC18:AC58"),
Type:=xlFillDefault
Range("AC18:AC58").Select

Range("AK19:AK58").Interior.ColorIndex = 3
Selection.AutoFill Destination:=Range("AK18:AK58"),
Type:=xlFillDefault
Range("AK18:AK58").Select

Range("AS19:AS58").Interior.ColorIndex = 7
Selection.AutoFill Destination:=Range("AS18:AS58"),
Type:=xlFillDefault
Range("AS18:AS58").Select

Else

Range("M19:M58").Interior.ColorIndex = 36
Range("M19:M58").Value = 0

Range("U19:U58").Interior.ColorIndex = 36
Range("U19:U58").Value = 0

Range("AC19:AC58").Interior.ColorIndex = 36
Range("AC19:AC58").Value = 0

Range("AK19:AK58").Interior.ColorIndex = 36
Range("AK19:AK58").Value = 0

Range("AS19:AS58").Interior.ColorIndex = 36
Range("AS19:AS58").Value = 0

End If


End Sub

 

[michael.beckinsale]

Hi Otto,

Try replacing each bit of your code that looks like this:

Range("M19:M58").Interior.ColorIndex = 4
Selection.AutoFill Destination:=Range("M18:M58"), Type:=xlFillDefault
Range("M18:M58").Select

With this

Range("M18").Copy Destination:= Range("M19:M58")

This will copy all of the properties of 'M18' to 'M19:M58' and the
because you are not selecting ranges the code is more efficient.

HTH

Michael

 

[Otter]

Thanks Michael. That works perfectly