I have a code that will autofill cells with the information listed in another cell. However, it takes forever for the code to run...I am assuming it takes so long to run because of how I have the code written. Is there a way this code could be modified?
Here is the basic code...It is written out 15 times (Autofill_Destination to Autofill_Destination15) with the highlighted letter in Red being the only thing that changes with the letters; H, J, L, N, T, V, X, Z, AB, AH, AJ, AL, AN, and AP
All of the Autofill_Destination codes are called from a code written on the main spreadsheet. The code is as follows.....
Any suggestions?
Here is the basic code...It is written out 15 times (Autofill_Destination to Autofill_Destination15) with the highlighted letter in Red being the only thing that changes with the letters; H, J, L, N, T, V, X, Z, AB, AH, AJ, AL, AN, and AP
Code:
Sub Autofill_Destination()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
xlCalc = Application.Calculation<o:p></o:p>
Application.Calculation = xlManual<o:p></o:p>
<o:p></o:p>
On Error GoTo CalcBack<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
<o:p></o:p>
If Range("[COLOR=red]F[/COLOR]16").Value >= Range("I11").Value And Range("[COLOR=red]F[/COLOR]16").Value <= Range("J11").Value Then<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Range("[COLOR=red]F[/COLOR]17,[COLOR=red]F[/COLOR]19,[COLOR=red]F[/COLOR]21").Value = Range("G11").Value<o:p></o:p>
<o:p></o:p>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("[COLOR=red]F[/COLOR]16").Value >= Range("I12").Value And Range("[COLOR=red]F[/COLOR]16").Value <= Range("J12").Value Then<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Range("[COLOR=red]F[/COLOR]17,[COLOR=red]F[/COLOR]19,[COLOR=red]F[/COLOR]21").Value = Range("G12").Value<o:p></o:p>
<o:p></o:p>
<st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("[COLOR=red]F[/COLOR]16").Value >= Range("M10").Value And Range("[COLOR=red]F[/COLOR]16").Value <= Range("N10").Value Then<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Range("[COLOR=red]F[/COLOR]17,[COLOR=red]F[/COLOR]19,[COLOR=red]F[/COLOR]21").Value = Range("K10").Value<o:p></o:p>
<o:p></o:p>
<st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("[COLOR=red]F[/COLOR]16").Value >= Range("M11").Value And Range("[COLOR=red]F[/COLOR]16").Value <= Range("N11").Value Then<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Range("[COLOR=red]F[/COLOR]17,[COLOR=red]F[/COLOR]19[COLOR=red],F[/COLOR]21").Value = Range("K11").Value<o:p></o:p>
<o:p></o:p>
<st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("[COLOR=red]F[/COLOR]16").Value >= Range("M12").Value And Range("[COLOR=red]F[/COLOR]16").Value <= Range("N12").Value Then<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Range("[COLOR=red]F[/COLOR]17,[COLOR=red]F[/COLOR]19,[COLOR=red]F[/COLOR]21").Value = Range("K12").Value<o:p></o:p>
<o:p></o:p>
Else<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Range("[COLOR=red]F[/COLOR]17,[COLOR=red]F[/COLOR]19,[COLOR=red]F[/COLOR]21").Value = 0<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
CalcBack:<o:p></o:p>
<o:p></o:p>
Application.Calculation = xlCalc<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
End Sub<o:p></o:p>
All of the Autofill_Destination codes are called from a code written on the main spreadsheet. The code is as follows.....
Code:
Private Sub Worksheet_change(ByVal Target As Range)<o:p></o:p>
<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
xlAuto = Application.Calculation<o:p></o:p>
<o:p></o:p>
Application.Calculation = xlAutomatic<o:p></o:p>
<o:p></o:p>
On Error GoTo AutoBack<o:p></o:p>
<o:p></o:p>
If Not Intersect(Target, [J11,J12,N10,N11,N12]) Is Nothing And Target.Cells.Count > 0 Then<o:p></o:p>
<o:p></o:p>
Call Autofill_Destination<o:p></o:p>
Call Autofill_Destination2<o:p></o:p>
Call Autofill_Destination3<o:p></o:p>
Call Autofill_Destination4<o:p></o:p>
Call Autofill_Destination5<o:p></o:p>
Call Autofill_Destination6<o:p></o:p>
Call Autofill_Destination7<o:p></o:p>
Call Autofill_Destination8<o:p></o:p>
Call Autofill_Destination9<o:p></o:p>
Call Autofill_Destination10<o:p></o:p>
Call Autofill_Destination11<o:p></o:p>
Call Autofill_Destination12<o:p></o:p>
Call Autofill_Destination13<o:p></o:p>
Call Autofill_Destination14<o:p></o:p>
Call Autofill_Destination15<o:p></o:p>
<o:p></o:p>
AutoBack:<o:p></o:p>
<o:p></o:p>
Application.Calculation = xlAuto<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
End Sub<o:p></o:p>
Any suggestions?