Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Cell with a macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Cell with a macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Cell with a macro
Thanks Michael. That works perfectly
"michael.beckinsale" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to search a text in a cell and copy the row to another set of cell | Excel Programming | |||
Creating Macro to copy information from cell into another cell using Add Comments | Excel Programming | |||
Using macro to copy a part of a cell content to next cell | Excel Discussion (Misc queries) | |||
macro to copy & paste cell data into another cell | Excel Programming | |||
Writing a macro to copy first cell down until next cell has data | Excel Programming |