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