Modifying a Code for Autofill

jm485

New Member
Joined
Mar 7, 2011
Messages
12
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

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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,215,516
Messages
6,125,286
Members
449,218
Latest member
Excel Master

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top