Too many lines of code

O

Oldbill62

New Member
Joined
Nov 26, 2014
Messages
23
Hi all
I have managed to write the following code which does the job. However I am sure there must be a better way of doing it. Any help is appreciated
Code: 
   Private Sub worksheet_Change(ByVal target As Range)   














Sheets("Report1").Range("B14").Value = Sheets("Download").Range("A4").Value
Sheets("Report1").Range("B15").Value = Sheets("Download").Range("b4").Value
Sheets("Report1").Range("B16").Value = Sheets("Download").Range("c4").Value
Sheets("Report1").Range("B17").Value = Sheets("Download").Range("i4").Value
Sheets("Report1").Range("B18").Value = Sheets("Download").Range("j4").Value
Sheets("Report1").Range("B19").Value = Sheets("Download").Range("L4").Value
Sheets("Report1").Range("e8").Value = Sheets("Download").Range("k4").Value
Sheets("Report1").Range("E9").Value = Sheets("Download").Range("O4").Value
Sheets("Report1").Range("E14").Value = Sheets("Download").Range("E4").Value
Sheets("Report1").Range("E15").Value = Sheets("Download").Range("F4").Value
Sheets("Report1").Range("E16").Value = Sheets("Download").Range("H4").Value
Sheets("Report1").Range("E18").Value = Sheets("Download").Range("M4").Value
Sheets("Report1").Range("F24").Value = Sheets("Download").Range("AM4").Value
Sheets("Report1").Range("F25").Value = Sheets("Download").Range("AN4").Value
Sheets("Report1").Range("F26").Value = Sheets("Download").Range("AL4").Value
Sheets("Report1").Range("F27").Value = Sheets("Download").Range("AO4").Value
Sheets("Report1").Range("F28").Value = Sheets("Download").Range("AP4").Value
Sheets("Report1").Range("F29").Value = Sheets("Download").Range("AQ4").Value
Sheets("Report1").Range("A31").Value = Sheets("Download").Range("AJ4").Value
Sheets("Report1").Range("B37").Value = Sheets("Download").Range("AD4").Value
Sheets("Report1").Range("B38").Value = Sheets("Download").Range("AE4").Value
Sheets("Report1").Range("B39").Value = Sheets("Download").Range("AF4").Value
Sheets("Report1").Range("B40").Value = Sheets("Download").Range("AG4").Value
Sheets("Report1").Range("B41").Value = Sheets("Download").Range("AH4").Value
Sheets("Report1").Range("D37").Value = Sheets("Download").Range("Y4").Value
Sheets("Report1").Range("D38").Value = Sheets("Download").Range("AA4").Value
Sheets("Report1").Range("D39").Value = Sheets("Download").Range("Z4").Value
Sheets("Report1").Range("D40").Value = Sheets("Download").Range("Ab4").Value
Sheets("Report1").Range("D42").Value = Sheets("Download").Range("AI4").Value
Sheets("Report1").Range("D49").Value = Sheets("Download").Range("AX4").Value
Sheets("Report1").Range("A55").Value = Sheets("Download").Range("AK4").Value




  Sheets("Report2").Range("B14").Value = Sheets("Download").Range("A5").Value
  Sheets("Report2").Range("B15").Value = Sheets("Download").Range("b5").Value
  Sheets("Report2").Range("B16").Value = Sheets("Download").Range("c5").Value
  Sheets("Report2").Range("B17").Value = Sheets("Download").Range("i5").Value
  Sheets("Report2").Range("B18").Value = Sheets("Download").Range("j5").Value
  Sheets("Report2").Range("B19").Value = Sheets("Download").Range("L5").Value
  Sheets("Report2").Range("e8").Value = Sheets("Download").Range("k5").Value
  Sheets("Report2").Range("E9").Value = Sheets("Download").Range("O5").Value
  Sheets("Report2").Range("E14").Value = Sheets("Download").Range("E5").Value
  Sheets("Report2").Range("E15").Value = Sheets("Download").Range("F5").Value
  Sheets("Report2").Range("E16").Value = Sheets("Download").Range("H5").Value
  Sheets("Report2").Range("E18").Value = Sheets("Download").Range("M5").Value
  Sheets("Report2").Range("F24").Value = Sheets("Download").Range("AM5").Value
  Sheets("Report2").Range("F25").Value = Sheets("Download").Range("AN5").Value
  Sheets("Report2").Range("F26").Value = Sheets("Download").Range("AL5").Value
  Sheets("Report2").Range("F27").Value = Sheets("Download").Range("AO5").Value
  Sheets("Report2").Range("F28").Value = Sheets("Download").Range("AP5").Value
  Sheets("Report2").Range("F29").Value = Sheets("Download").Range("AQ5").Value
  Sheets("Report2").Range("A31").Value = Sheets("Download").Range("AJ5").Value
  Sheets("Report2").Range("B37").Value = Sheets("Download").Range("AD5").Value
  Sheets("Report2").Range("B38").Value = Sheets("Download").Range("AE5").Value
  Sheets("Report2").Range("B39").Value = Sheets("Download").Range("AF5").Value
  Sheets("Report2").Range("B40").Value = Sheets("Download").Range("AG5").Value
  Sheets("Report2").Range("B41").Value = Sheets("Download").Range("AH5").Value
  Sheets("Report2").Range("D37").Value = Sheets("Download").Range("Y5").Value
  Sheets("Report2").Range("D38").Value = Sheets("Download").Range("AA5").Value
  Sheets("Report2").Range("D39").Value = Sheets("Download").Range("Z5").Value
  Sheets("Report2").Range("D40").Value = Sheets("Download").Range("Ab5").Value
  Sheets("Report2").Range("D42").Value = Sheets("Download").Range("AI5").Value
  Sheets("Report2").Range("D49").Value = Sheets("Download").Range("AX5").Value
  Sheets("Report2").Range("A55").Value = Sheets("Download").Range("AK5").Value
    
     Sheets("Report3").Range("B14").Value = Sheets("Download").Range("A6").Value
     Sheets("Report3").Range("B15").Value = Sheets("Download").Range("b6").Value
     Sheets("Report3").Range("B16").Value = Sheets("Download").Range("c6").Value
     Sheets("Report3").Range("B17").Value = Sheets("Download").Range("i6").Value
     Sheets("Report3").Range("B18").Value = Sheets("Download").Range("j6").Value
     Sheets("Report3").Range("e8").Value = Sheets("Download").Range("k6").Value
     Sheets("Report3").Range("B19").Value = Sheets("Download").Range("L6").Value
     Sheets("Report3").Range("E9").Value = Sheets("Download").Range("O6").Value
     Sheets("Report3").Range("E14").Value = Sheets("Download").Range("E6").Value
     Sheets("Report3").Range("E15").Value = Sheets("Download").Range("F6").Value
     Sheets("Report3").Range("E16").Value = Sheets("Download").Range("H6").Value
     Sheets("Report3").Range("E18").Value = Sheets("Download").Range("M6").Value
     Sheets("Report3").Range("F24").Value = Sheets("Download").Range("AM6").Value
     Sheets("Report3").Range("F25").Value = Sheets("Download").Range("AN6").Value
     Sheets("Report3").Range("F26").Value = Sheets("Download").Range("AL6").Value
     Sheets("Report3").Range("F27").Value = Sheets("Download").Range("AO6").Value
     Sheets("Report3").Range("F28").Value = Sheets("Download").Range("AP6").Value
     Sheets("Report3").Range("F29").Value = Sheets("Download").Range("AQ6").Value
     Sheets("Report3").Range("A31").Value = Sheets("Download").Range("AJ6").Value
     Sheets("Report3").Range("B37").Value = Sheets("Download").Range("AD6").Value
     Sheets("Report3").Range("B38").Value = Sheets("Download").Range("AE6").Value
     Sheets("Report3").Range("B39").Value = Sheets("Download").Range("AF6").Value
     Sheets("Report3").Range("B40").Value = Sheets("Download").Range("AG6").Value
     Sheets("Report3").Range("B41").Value = Sheets("Download").Range("AH6").Value
     Sheets("Report3").Range("D37").Value = Sheets("Download").Range("Y6").Value
     Sheets("Report3").Range("D38").Value = Sheets("Download").Range("AA6").Value
     Sheets("Report3").Range("D39").Value = Sheets("Download").Range("Z6").Value
     Sheets("Report3").Range("D40").Value = Sheets("Download").Range("Ab6").Value
     Sheets("Report3").Range("D42").Value = Sheets("Download").Range("AI6").Value
     Sheets("Report3").Range("D49").Value = Sheets("Download").Range("AX6").Value
     Sheets("Report3").Range("A55").Value = Sheets("Download").Range("AK6").Value


         Sheets("Report4").Range("B14").Value = Sheets("Download").Range("A7").Value
         Sheets("Report4").Range("B15").Value = Sheets("Download").Range("b7").Value
         Sheets("Report4").Range("B16").Value = Sheets("Download").Range("c7").Value
         Sheets("Report4").Range("B17").Value = Sheets("Download").Range("i7").Value
         Sheets("Report4").Range("B18").Value = Sheets("Download").Range("j7").Value
         Sheets("Report4").Range("B19").Value = Sheets("Download").Range("L7").Value
         Sheets("Report4").Range("e8").Value = Sheets("Download").Range("k7").Value
         Sheets("Report4").Range("E9").Value = Sheets("Download").Range("O7").Value
         Sheets("Report4").Range("E14").Value = Sheets("Download").Range("E7").Value
         Sheets("Report4").Range("E15").Value = Sheets("Download").Range("F7").Value
         Sheets("Report4").Range("E16").Value = Sheets("Download").Range("H7").Value
         Sheets("Report4").Range("E18").Value = Sheets("Download").Range("M7").Value
         Sheets("Report4").Range("F24").Value = Sheets("Download").Range("AM7").Value
         Sheets("Report4").Range("F25").Value = Sheets("Download").Range("AN7").Value
         Sheets("Report4").Range("F26").Value = Sheets("Download").Range("AL7").Value
         Sheets("Report4").Range("F27").Value = Sheets("Download").Range("AO7").Value
         Sheets("Report4").Range("F28").Value = Sheets("Download").Range("AP7").Value
         Sheets("Report4").Range("F29").Value = Sheets("Download").Range("AQ7").Value
         Sheets("Report4").Range("A31").Value = Sheets("Download").Range("AJ7").Value
         Sheets("Report4").Range("B37").Value = Sheets("Download").Range("AD7").Value
         Sheets("Report4").Range("B38").Value = Sheets("Download").Range("AE7").Value
         Sheets("Report4").Range("B39").Value = Sheets("Download").Range("AF7").Value
         Sheets("Report4").Range("B40").Value = Sheets("Download").Range("AG7").Value
         Sheets("Report4").Range("B41").Value = Sheets("Download").Range("AH7").Value
         Sheets("Report4").Range("D37").Value = Sheets("Download").Range("Y7").Value
         Sheets("Report4").Range("D38").Value = Sheets("Download").Range("AA7").Value
         Sheets("Report4").Range("D39").Value = Sheets("Download").Range("Z7").Value
         Sheets("Report4").Range("D40").Value = Sheets("Download").Range("Ab7").Value
         Sheets("Report4").Range("D42").Value = Sheets("Download").Range("AI7").Value
         Sheets("Report4").Range("D49").Value = Sheets("Download").Range("AX7").Value
         Sheets("Report4").Range("A55").Value = Sheets("Download").Range("AK7").Value


            Sheets("Report5").Range("B14").Value = Sheets("Download").Range("A8").Value
            Sheets("Report5").Range("B15").Value = Sheets("Download").Range("b8").Value
            Sheets("Report5").Range("B16").Value = Sheets("Download").Range("c8").Value
            Sheets("Report5").Range("B17").Value = Sheets("Download").Range("i8").Value
            Sheets("Report5").Range("B18").Value = Sheets("Download").Range("j8").Value
            Sheets("Report5").Range("B19").Value = Sheets("Download").Range("L8").Value
            Sheets("Report5").Range("e8").Value = Sheets("Download").Range("k8").Value
            Sheets("Report5").Range("E9").Value = Sheets("Download").Range("O8").Value
            Sheets("Report5").Range("E14").Value = Sheets("Download").Range("E8").Value
            Sheets("Report5").Range("E15").Value = Sheets("Download").Range("F8").Value
            Sheets("Report5").Range("E16").Value = Sheets("Download").Range("H8").Value
            Sheets("Report5").Range("E18").Value = Sheets("Download").Range("M8").Value
            Sheets("Report5").Range("F24").Value = Sheets("Download").Range("AM8").Value
            Sheets("Report5").Range("F25").Value = Sheets("Download").Range("AN8").Value
            Sheets("Report5").Range("F26").Value = Sheets("Download").Range("AL8").Value
            Sheets("Report5").Range("F27").Value = Sheets("Download").Range("AO8").Value
            Sheets("Report5").Range("F28").Value = Sheets("Download").Range("AP8").Value
            Sheets("Report5").Range("F29").Value = Sheets("Download").Range("AQ8").Value
            Sheets("Report5").Range("A31").Value = Sheets("Download").Range("AJ8").Value
            Sheets("Report5").Range("B37").Value = Sheets("Download").Range("AD8").Value
            Sheets("Report5").Range("B38").Value = Sheets("Download").Range("AE8").Value
            Sheets("Report5").Range("B39").Value = Sheets("Download").Range("AF8").Value
            Sheets("Report5").Range("B40").Value = Sheets("Download").Range("AG8").Value
            Sheets("Report5").Range("B41").Value = Sheets("Download").Range("AH8").Value
            Sheets("Report5").Range("D37").Value = Sheets("Download").Range("Y8").Value
            Sheets("Report5").Range("D38").Value = Sheets("Download").Range("AA8").Value
            Sheets("Report5").Range("D39").Value = Sheets("Download").Range("Z8").Value
            Sheets("Report5").Range("D40").Value = Sheets("Download").Range("Ab8").Value
            Sheets("Report5").Range("D42").Value = Sheets("Download").Range("AI8").Value
            Sheets("Report5").Range("D49").Value = Sheets("Download").Range("AX8").Value
            Sheets("Report5").Range("A55").Value = Sheets("Download").Range("AK8").Value


                        Sheets("Report6").Range("B14").Value = Sheets("Download").Range("A9").Value
                        Sheets("Report6").Range("B15").Value = Sheets("Download").Range("b9").Value
                        Sheets("Report6").Range("B16").Value = Sheets("Download").Range("c9").Value
                        Sheets("Report6").Range("B17").Value = Sheets("Download").Range("i9").Value
                        Sheets("Report6").Range("B18").Value = Sheets("Download").Range("j9").Value
                        Sheets("Report6").Range("B19").Value = Sheets("Download").Range("L9").Value
                        Sheets("Report6").Range("e8").Value = Sheets("Download").Range("k9").Value
                        Sheets("Report6").Range("E9").Value = Sheets("Download").Range("O9").Value
                        Sheets("Report6").Range("E14").Value = Sheets("Download").Range("E9").Value
                        Sheets("Report6").Range("E15").Value = Sheets("Download").Range("F9").Value
                        Sheets("Report6").Range("E16").Value = Sheets("Download").Range("H9").Value
                        Sheets("Report6").Range("E18").Value = Sheets("Download").Range("M9").Value
                        Sheets("Report6").Range("F24").Value = Sheets("Download").Range("AM9").Value
                        Sheets("Report6").Range("F25").Value = Sheets("Download").Range("AN9").Value
                        Sheets("Report6").Range("F26").Value = Sheets("Download").Range("AL9").Value
                        Sheets("Report6").Range("F27").Value = Sheets("Download").Range("AO9").Value
                        Sheets("Report6").Range("F28").Value = Sheets("Download").Range("AP9").Value
                        Sheets("Report6").Range("F29").Value = Sheets("Download").Range("AQ9").Value
                        Sheets("Report6").Range("A31").Value = Sheets("Download").Range("AJ9").Value
                        Sheets("Report6").Range("B37").Value = Sheets("Download").Range("AD9").Value
                        Sheets("Report6").Range("B38").Value = Sheets("Download").Range("AE9").Value
                        Sheets("Report6").Range("B39").Value = Sheets("Download").Range("AF9").Value
                        Sheets("Report6").Range("B40").Value = Sheets("Download").Range("AG9").Value
                        Sheets("Report6").Range("B41").Value = Sheets("Download").Range("AH9").Value
                        Sheets("Report6").Range("D37").Value = Sheets("Download").Range("Y9").Value
                        Sheets("Report6").Range("D38").Value = Sheets("Download").Range("AA9").Value
                        Sheets("Report6").Range("D39").Value = Sheets("Download").Range("Z9").Value
                        Sheets("Report6").Range("D40").Value = Sheets("Download").Range("Ab9").Value
                        Sheets("Report6").Range("D42").Value = Sheets("Download").Range("AI9").Value
                        Sheets("Report6").Range("D49").Value = Sheets("Download").Range("AX9").Value
                        Sheets("Report6").Range("A55").Value = Sheets("Download").Range("AK9").Value


                            Sheets("Report7").Range("B14").Value = Sheets("Download").Range("A10").Value
                            Sheets("Report7").Range("B15").Value = Sheets("Download").Range("b10").Value
                            Sheets("Report7").Range("B16").Value = Sheets("Download").Range("c10").Value
                            Sheets("Report7").Range("B17").Value = Sheets("Download").Range("i10").Value
                            Sheets("Report7").Range("B18").Value = Sheets("Download").Range("j10").Value
                            Sheets("Report7").Range("B19").Value = Sheets("Download").Range("l10").Value
                            Sheets("Report7").Range("e8").Value = Sheets("Download").Range("k10").Value
                            Sheets("Report7").Range("E9").Value = Sheets("Download").Range("O10").Value
                            Sheets("Report7").Range("E14").Value = Sheets("Download").Range("E10").Value
                            Sheets("Report7").Range("E15").Value = Sheets("Download").Range("F10").Value
                            Sheets("Report7").Range("E16").Value = Sheets("Download").Range("H10").Value
                            Sheets("Report7").Range("E18").Value = Sheets("Download").Range("M10").Value
                            Sheets("Report7").Range("F24").Value = Sheets("Download").Range("AM10").Value
                            Sheets("Report7").Range("F25").Value = Sheets("Download").Range("AN10").Value
                            Sheets("Report7").Range("F26").Value = Sheets("Download").Range("AL10").Value
                            Sheets("Report7").Range("F27").Value = Sheets("Download").Range("AO10").Value
                            Sheets("Report7").Range("F28").Value = Sheets("Download").Range("AP10").Value
                            Sheets("Report7").Range("F29").Value = Sheets("Download").Range("AQ10").Value
                            Sheets("Report7").Range("A31").Value = Sheets("Download").Range("AJ10").Value
                            Sheets("Report7").Range("B37").Value = Sheets("Download").Range("AD10").Value
                            Sheets("Report7").Range("B38").Value = Sheets("Download").Range("AE10").Value
                            Sheets("Report7").Range("B39").Value = Sheets("Download").Range("AF10").Value
                            Sheets("Report7").Range("B40").Value = Sheets("Download").Range("AG10").Value
                            Sheets("Report7").Range("B41").Value = Sheets("Download").Range("AH10").Value
                            Sheets("Report7").Range("D37").Value = Sheets("Download").Range("Y10").Value
                            Sheets("Report7").Range("D38").Value = Sheets("Download").Range("AA10").Value
                            Sheets("Report7").Range("D39").Value = Sheets("Download").Range("Z10").Value
                            Sheets("Report7").Range("D40").Value = Sheets("Download").Range("Ab10").Value
                            Sheets("Report7").Range("D42").Value = Sheets("Download").Range("AI10").Value
                            Sheets("Report7").Range("D49").Value = Sheets("Download").Range("AX10").Value
                            Sheets("Report7").Range("A55").Value = Sheets("Download").Range("AK10").Value
                                
                                    Sheets("Report8").Range("B14").Value = Sheets("Download").Range("A11").Value
                                    Sheets("Report8").Range("B15").Value = Sheets("Download").Range("b11").Value
                                    Sheets("Report8").Range("B16").Value = Sheets("Download").Range("c11").Value
                                    Sheets("Report8").Range("B17").Value = Sheets("Download").Range("i11").Value
                                    Sheets("Report8").Range("B18").Value = Sheets("Download").Range("j11").Value
                                    Sheets("Report8").Range("B19").Value = Sheets("Download").Range("l11").Value
                                    Sheets("Report8").Range("e8").Value = Sheets("Download").Range("k11").Value
                                    Sheets("Report8").Range("E9").Value = Sheets("Download").Range("O11").Value
                                    Sheets("Report8").Range("E14").Value = Sheets("Download").Range("E11").Value
                                    Sheets("Report8").Range("E15").Value = Sheets("Download").Range("F11").Value
                                    Sheets("Report8").Range("E16").Value = Sheets("Download").Range("H11").Value
                                    Sheets("Report8").Range("E18").Value = Sheets("Download").Range("M11").Value
                                    Sheets("Report8").Range("F24").Value = Sheets("Download").Range("AM11").Value
                                    Sheets("Report8").Range("F25").Value = Sheets("Download").Range("AN11").Value
                                    Sheets("Report8").Range("F26").Value = Sheets("Download").Range("AL11").Value
                                    Sheets("Report8").Range("F27").Value = Sheets("Download").Range("AO11").Value
                                    Sheets("Report8").Range("F28").Value = Sheets("Download").Range("AP11").Value
                                    Sheets("Report8").Range("F29").Value = Sheets("Download").Range("AQ11").Value
                                    Sheets("Report8").Range("A31").Value = Sheets("Download").Range("AJ11").Value
                                    Sheets("Report8").Range("B37").Value = Sheets("Download").Range("AD11").Value
                                    Sheets("Report8").Range("B38").Value = Sheets("Download").Range("AE11").Value
                                    Sheets("Report8").Range("B39").Value = Sheets("Download").Range("AF11").Value
                                    Sheets("Report8").Range("B40").Value = Sheets("Download").Range("AG11").Value
                                    Sheets("Report8").Range("B41").Value = Sheets("Download").Range("AH11").Value
                                    Sheets("Report8").Range("D37").Value = Sheets("Download").Range("Y11").Value
                                    Sheets("Report8").Range("D38").Value = Sheets("Download").Range("AA11").Value
                                    Sheets("Report8").Range("D39").Value = Sheets("Download").Range("Z11").Value
                                    Sheets("Report8").Range("D40").Value = Sheets("Download").Range("Ab11").Value
                                    Sheets("Report8").Range("D42").Value = Sheets("Download").Range("AI11").Value
                                    Sheets("Report8").Range("D49").Value = Sheets("Download").Range("AX11").Value
                                    Sheets("Report8").Range("A55").Value = Sheets("Download").Range("AK11").Value


                                        Sheets("Report9").Range("B14").Value = Sheets("Download").Range("A12").Value
                                        Sheets("Report9").Range("B15").Value = Sheets("Download").Range("b12").Value
                                        Sheets("Report9").Range("B16").Value = Sheets("Download").Range("c12").Value
                                        Sheets("Report9").Range("B17").Value = Sheets("Download").Range("i12").Value
                                        Sheets("Report9").Range("B18").Value = Sheets("Download").Range("j12").Value
                                        Sheets("Report9").Range("B19").Value = Sheets("Download").Range("l12").Value
                                        Sheets("Report9").Range("e8").Value = Sheets("Download").Range("k12").Value
                                        Sheets("Report9").Range("E9").Value = Sheets("Download").Range("O12").Value
                                        Sheets("Report9").Range("E14").Value = Sheets("Download").Range("E12").Value
                                        Sheets("Report9").Range("E15").Value = Sheets("Download").Range("F12").Value
                                        Sheets("Report9").Range("E16").Value = Sheets("Download").Range("H12").Value
                                        Sheets("Report9").Range("E18").Value = Sheets("Download").Range("M12").Value
                                        Sheets("Report9").Range("F24").Value = Sheets("Download").Range("AM12").Value
                                        Sheets("Report9").Range("F25").Value = Sheets("Download").Range("AN12").Value
                                        Sheets("Report9").Range("F26").Value = Sheets("Download").Range("AL12").Value
                                        Sheets("Report9").Range("F27").Value = Sheets("Download").Range("AO12").Value
                                        Sheets("Report9").Range("F28").Value = Sheets("Download").Range("AP12").Value
                                        Sheets("Report9").Range("F29").Value = Sheets("Download").Range("AQ12").Value
                                        Sheets("Report9").Range("A31").Value = Sheets("Download").Range("AJ12").Value
                                        Sheets("Report9").Range("B37").Value = Sheets("Download").Range("AD12").Value
                                        Sheets("Report9").Range("B38").Value = Sheets("Download").Range("AE12").Value
                                        Sheets("Report9").Range("B39").Value = Sheets("Download").Range("AF12").Value
                                        Sheets("Report9").Range("B40").Value = Sheets("Download").Range("AG12").Value
                                        Sheets("Report9").Range("B41").Value = Sheets("Download").Range("AH12").Value
                                        Sheets("Report9").Range("D37").Value = Sheets("Download").Range("Y12").Value
                                        Sheets("Report9").Range("D38").Value = Sheets("Download").Range("AA12").Value
                                        Sheets("Report9").Range("D39").Value = Sheets("Download").Range("Z12").Value
                                        Sheets("Report9").Range("D40").Value = Sheets("Download").Range("Ab12").Value
                                        Sheets("Report9").Range("D42").Value = Sheets("Download").Range("AI12").Value
                                        Sheets("Report9").Range("D49").Value = Sheets("Download").Range("AX12").Value
                                        Sheets("Report9").Range("A55").Value = Sheets("Download").Range("AK12").Value
                        
                                            Sheets("Report10").Range("B14").Value = Sheets("Download").Range("A13").Value
                                            Sheets("Report10").Range("B15").Value = Sheets("Download").Range("b13").Value
                                            Sheets("Report10").Range("B16").Value = Sheets("Download").Range("c13").Value
                                            Sheets("Report10").Range("B17").Value = Sheets("Download").Range("i13").Value
                                            Sheets("Report10").Range("B18").Value = Sheets("Download").Range("j13").Value
                                            Sheets("Report10").Range("B19").Value = Sheets("Download").Range("l13").Value
                                            Sheets("Report10").Range("e8").Value = Sheets("Download").Range("k13").Value
                                            Sheets("Report10").Range("E9").Value = Sheets("Download").Range("O13").Value
                                            Sheets("Report10").Range("E14").Value = Sheets("Download").Range("E13").Value
                                            Sheets("Report10").Range("E15").Value = Sheets("Download").Range("F13").Value
                                            Sheets("Report10").Range("E16").Value = Sheets("Download").Range("H13").Value
                                            Sheets("Report10").Range("E18").Value = Sheets("Download").Range("M13").Value
                                            Sheets("Report10").Range("F24").Value = Sheets("Download").Range("AM13").Value
                                            Sheets("Report10").Range("F25").Value = Sheets("Download").Range("AN13").Value
                                            Sheets("Report10").Range("F26").Value = Sheets("Download").Range("AL13").Value
                                            Sheets("Report10").Range("F27").Value = Sheets("Download").Range("AO13").Value
                                            Sheets("Report10").Range("F28").Value = Sheets("Download").Range("AP13").Value
                                            Sheets("Report10").Range("F29").Value = Sheets("Download").Range("AQ13").Value
                                            Sheets("Report10").Range("A31").Value = Sheets("Download").Range("AJ13").Value
                                            Sheets("Report10").Range("B37").Value = Sheets("Download").Range("AD13").Value
                                            Sheets("Report10").Range("B38").Value = Sheets("Download").Range("AE13").Value
                                            Sheets("Report10").Range("B39").Value = Sheets("Download").Range("AF13").Value
                                            Sheets("Report10").Range("B40").Value = Sheets("Download").Range("AG13").Value
                                            Sheets("Report10").Range("B41").Value = Sheets("Download").Range("AH13").Value
                                            Sheets("Report10").Range("D37").Value = Sheets("Download").Range("Y13").Value
                                            Sheets("Report10").Range("D38").Value = Sheets("Download").Range("AA13").Value
                                            Sheets("Report10").Range("D39").Value = Sheets("Download").Range("Z13").Value
                                            Sheets("Report10").Range("D40").Value = Sheets("Download").Range("Ab13").Value
                                            Sheets("Report10").Range("D42").Value = Sheets("Download").Range("AI13").Value
                                            Sheets("Report10").Range("D49").Value = Sheets("Download").Range("AX13").Value
                                            Sheets("Report10").Range("A55").Value = Sheets("Download").Range("AK13").Value


                                                Sheets("Report11").Range("B14").Value = Sheets("Download").Range("A14").Value
                                                Sheets("Report11").Range("B15").Value = Sheets("Download").Range("b14").Value
                                                Sheets("Report11").Range("B16").Value = Sheets("Download").Range("c14").Value
                                                Sheets("Report11").Range("B17").Value = Sheets("Download").Range("i14").Value
                                                Sheets("Report11").Range("B18").Value = Sheets("Download").Range("j14").Value
                                                Sheets("Report11").Range("B19").Value = Sheets("Download").Range("l14").Value
                                                Sheets("Report11").Range("e8").Value = Sheets("Download").Range("k14").Value
                                                Sheets("Report11").Range("E9").Value = Sheets("Download").Range("O14").Value
                                                Sheets("Report11").Range("E14").Value = Sheets("Download").Range("E14").Value
                                                Sheets("Report11").Range("E15").Value = Sheets("Download").Range("F14").Value
                                                Sheets("Report11").Range("E16").Value = Sheets("Download").Range("H14").Value
                                                Sheets("Report11").Range("E18").Value = Sheets("Download").Range("M14").Value
                                                Sheets("Report11").Range("F24").Value = Sheets("Download").Range("AM14").Value
                                                Sheets("Report11").Range("F25").Value = Sheets("Download").Range("AN14").Value
                                                Sheets("Report11").Range("F26").Value = Sheets("Download").Range("AL14").Value
                                                Sheets("Report11").Range("F27").Value = Sheets("Download").Range("AO14").Value
                                                Sheets("Report11").Range("F28").Value = Sheets("Download").Range("AP14").Value
                                                Sheets("Report11").Range("F29").Value = Sheets("Download").Range("AQ14").Value
                                                Sheets("Report11").Range("A31").Value = Sheets("Download").Range("AJ14").Value
                                                Sheets("Report11").Range("B37").Value = Sheets("Download").Range("AD14").Value
                                                Sheets("Report11").Range("B38").Value = Sheets("Download").Range("AE14").Value
                                                Sheets("Report11").Range("B39").Value = Sheets("Download").Range("AF14").Value
                                                Sheets("Report11").Range("B40").Value = Sheets("Download").Range("AG14").Value
                                                Sheets("Report11").Range("B41").Value = Sheets("Download").Range("AH14").Value
                                                Sheets("Report11").Range("D37").Value = Sheets("Download").Range("Y14").Value
                                                Sheets("Report11").Range("D38").Value = Sheets("Download").Range("AA14").Value
                                                Sheets("Report11").Range("D39").Value = Sheets("Download").Range("Z14").Value
                                                Sheets("Report11").Range("D40").Value = Sheets("Download").Range("Ab14").Value
                                                Sheets("Report11").Range("D42").Value = Sheets("Download").Range("AI14").Value
                                                Sheets("Report11").Range("D49").Value = Sheets("Download").Range("AX14").Value
                                                Sheets("Report11").Range("A55").Value = Sheets("Download").Range("AK14").Value
                                                    
                                                    Sheets("Report12").Range("B14").Value = Sheets("Download").Range("A15").Value
                                                    Sheets("Report12").Range("B15").Value = Sheets("Download").Range("b15").Value
                                                    Sheets("Report12").Range("B16").Value = Sheets("Download").Range("c15").Value
                                                    Sheets("Report12").Range("B17").Value = Sheets("Download").Range("i15").Value
                                                    Sheets("Report12").Range("B18").Value = Sheets("Download").Range("j15").Value
                                                    Sheets("Report12").Range("B19").Value = Sheets("Download").Range("l15").Value
                                                    Sheets("Report12").Range("e8").Value = Sheets("Download").Range("k15").Value
                                                    Sheets("Report12").Range("E9").Value = Sheets("Download").Range("O15").Value
                                                    Sheets("Report12").Range("E14").Value = Sheets("Download").Range("E15").Value
                                                    Sheets("Report12").Range("E15").Value = Sheets("Download").Range("F15").Value
                                                    Sheets("Report12").Range("E16").Value = Sheets("Download").Range("H15").Value
                                                    Sheets("Report12").Range("E18").Value = Sheets("Download").Range("M15").Value
                                                    Sheets("Report12").Range("F24").Value = Sheets("Download").Range("AM15").Value
                                                    Sheets("Report12").Range("F25").Value = Sheets("Download").Range("AN15").Value
                                                    Sheets("Report12").Range("F26").Value = Sheets("Download").Range("AL15").Value
                                                    Sheets("Report12").Range("F27").Value = Sheets("Download").Range("AO15").Value
                                                    Sheets("Report12").Range("F28").Value = Sheets("Download").Range("AP15").Value
                                                    Sheets("Report12").Range("F29").Value = Sheets("Download").Range("AQ15").Value
                                                    Sheets("Report12").Range("A31").Value = Sheets("Download").Range("AJ15").Value
                                                    Sheets("Report12").Range("B37").Value = Sheets("Download").Range("AD15").Value
                                                    Sheets("Report12").Range("B38").Value = Sheets("Download").Range("AE15").Value
                                                    Sheets("Report12").Range("B39").Value = Sheets("Download").Range("AF15").Value
                                                    Sheets("Report12").Range("B40").Value = Sheets("Download").Range("AG14").Value
                                                    Sheets("Report12").Range("B41").Value = Sheets("Download").Range("AH14").Value
                                                    Sheets("Report12").Range("D37").Value = Sheets("Download").Range("Y14").Value
                                                    Sheets("Report12").Range("D38").Value = Sheets("Download").Range("AA14").Value
                                                    Sheets("Report12").Range("D39").Value = Sheets("Download").Range("Z14").Value
                                                    Sheets("Report12").Range("D40").Value = Sheets("Download").Range("Ab14").Value
                                                    Sheets("Report12").Range("D42").Value = Sheets("Download").Range("AI14").Value
                                                    Sheets("Report12").Range("D49").Value = Sheets("Download").Range("AX14").Value
                                                    Sheets("Report12").Range("A55").Value = Sheets("Download").Range("AK14").Value


                                                            Sheets("Report13").Range("B14").Value = Sheets("Download").Range("A16").Value
                                                            Sheets("Report13").Range("B15").Value = Sheets("Download").Range("b16").Value
                                                            Sheets("Report13").Range("B16").Value = Sheets("Download").Range("c16").Value
                                                            Sheets("Report13").Range("B17").Value = Sheets("Download").Range("i16").Value
                                                            Sheets("Report13").Range("B18").Value = Sheets("Download").Range("j16").Value
                                                            Sheets("Report13").Range("B19").Value = Sheets("Download").Range("l16").Value
                                                            Sheets("Report13").Range("e8").Value = Sheets("Download").Range("k16").Value
                                                            Sheets("Report13").Range("E9").Value = Sheets("Download").Range("O16").Value
                                                            Sheets("Report13").Range("E14").Value = Sheets("Download").Range("E16").Value
                                                            Sheets("Report13").Range("E15").Value = Sheets("Download").Range("F16").Value
                                                            Sheets("Report13").Range("E16").Value = Sheets("Download").Range("H16").Value
                                                            Sheets("Report13").Range("E18").Value = Sheets("Download").Range("M16").Value
                                                            Sheets("Report13").Range("F24").Value = Sheets("Download").Range("AM16").Value
                                                            Sheets("Report13").Range("F25").Value = Sheets("Download").Range("AN16").Value
                                                            Sheets("Report13").Range("F26").Value = Sheets("Download").Range("AL16").Value
                                                            Sheets("Report13").Range("F27").Value = Sheets("Download").Range("AO16").Value
                                                            Sheets("Report13").Range("F28").Value = Sheets("Download").Range("AP16").Value
                                                            Sheets("Report13").Range("F29").Value = Sheets("Download").Range("AQ16").Value
                                                            Sheets("Report13").Range("A31").Value = Sheets("Download").Range("AJ16").Value
                                                            Sheets("Report13").Range("B37").Value = Sheets("Download").Range("AD16").Value
                                                            Sheets("Report13").Range("B38").Value = Sheets("Download").Range("AE16").Value
                                                            Sheets("Report13").Range("B39").Value = Sheets("Download").Range("AF16").Value
                                                            Sheets("Report13").Range("B40").Value = Sheets("Download").Range("AG16").Value
                                                            Sheets("Report13").Range("B41").Value = Sheets("Download").Range("AH16").Value
                                                            Sheets("Report13").Range("D37").Value = Sheets("Download").Range("Y16").Value
                                                            Sheets("Report13").Range("D38").Value = Sheets("Download").Range("AA16").Value
                                                            Sheets("Report13").Range("D39").Value = Sheets("Download").Range("Z16").Value
                                                            Sheets("Report13").Range("D40").Value = Sheets("Download").Range("Ab16").Value
                                                            Sheets("Report13").Range("D42").Value = Sheets("Download").Range("AI16").Value
                                                            Sheets("Report13").Range("D49").Value = Sheets("Download").Range("AX16").Value
                                                            Sheets("Report13").Range("A55").Value = Sheets("Download").Range("AK16").Value
                                                                
                                                                Sheets("Report14").Range("B14").Value = Sheets("Download").Range("A17").Value
                                                                Sheets("Report14").Range("B15").Value = Sheets("Download").Range("b17").Value
                                                                Sheets("Report14").Range("B16").Value = Sheets("Download").Range("c17").Value
                                                                Sheets("Report14").Range("B17").Value = Sheets("Download").Range("i17").Value
                                                                Sheets("Report14").Range("B18").Value = Sheets("Download").Range("j17").Value
                                                                Sheets("Report14").Range("B19").Value = Sheets("Download").Range("l17").Value
                                                                Sheets("Report14").Range("e8").Value = Sheets("Download").Range("k17").Value
                                                                Sheets("Report14").Range("E9").Value = Sheets("Download").Range("O17").Value
                                                                Sheets("Report14").Range("E14").Value = Sheets("Download").Range("E17").Value
                                                                Sheets("Report14").Range("E15").Value = Sheets("Download").Range("F17").Value
                                                                Sheets("Report14").Range("E16").Value = Sheets("Download").Range("H17").Value
                                                                Sheets("Report14").Range("E18").Value = Sheets("Download").Range("M17").Value
                                                                Sheets("Report14").Range("F24").Value = Sheets("Download").Range("AM17").Value
                                                                Sheets("Report14").Range("F25").Value = Sheets("Download").Range("AN17").Value
                                                                Sheets("Report14").Range("F26").Value = Sheets("Download").Range("AL17").Value
                                                                Sheets("Report14").Range("F27").Value = Sheets("Download").Range("AO17").Value
                                                                Sheets("Report14").Range("F28").Value = Sheets("Download").Range("AP17").Value
                                                                Sheets("Report14").Range("F29").Value = Sheets("Download").Range("AQ17").Value
                                                                Sheets("Report14").Range("A31").Value = Sheets("Download").Range("AJ17").Value
                                                                Sheets("Report14").Range("B37").Value = Sheets("Download").Range("AD17").Value
                                                                Sheets("Report14").Range("B38").Value = Sheets("Download").Range("AE17").Value
                                                                Sheets("Report14").Range("B39").Value = Sheets("Download").Range("AF17").Value
                                                                Sheets("Report14").Range("B40").Value = Sheets("Download").Range("AG17").Value
                                                                Sheets("Report14").Range("B41").Value = Sheets("Download").Range("AH17").Value
                                                                Sheets("Report14").Range("D37").Value = Sheets("Download").Range("Y17").Value
                                                                Sheets("Report14").Range("D38").Value = Sheets("Download").Range("AA17").Value
                                                                Sheets("Report14").Range("D39").Value = Sheets("Download").Range("Z17").Value
                                                                Sheets("Report14").Range("D40").Value = Sheets("Download").Range("Ab17").Value
                                                                Sheets("Report14").Range("D42").Value = Sheets("Download").Range("AI17").Value
                                                                Sheets("Report14").Range("D49").Value = Sheets("Download").Range("AX17").Value
                                                                Sheets("Report14").Range("A55").Value = Sheets("Download").Range("AK17").Value
    
                                                                    Sheets("Report15").Range("B14").Value = Sheets("Download").Range("A18").Value
                                                                    Sheets("Report15").Range("B15").Value = Sheets("Download").Range("b18").Value
                                                                    Sheets("Report15").Range("B16").Value = Sheets("Download").Range("c18").Value
                                                                    Sheets("Report15").Range("B17").Value = Sheets("Download").Range("i18").Value
                                                                    Sheets("Report15").Range("B18").Value = Sheets("Download").Range("j18").Value
                                                                    Sheets("Report15").Range("B19").Value = Sheets("Download").Range("l18").Value
                                                                    Sheets("Report15").Range("e8").Value = Sheets("Download").Range("k18").Value
                                                                    Sheets("Report15").Range("E9").Value = Sheets("Download").Range("O18").Value
                                                                    Sheets("Report15").Range("E14").Value = Sheets("Download").Range("E18").Value
                                                                    Sheets("Report15").Range("E15").Value = Sheets("Download").Range("F18").Value
                                                                    Sheets("Report15").Range("E16").Value = Sheets("Download").Range("H18").Value
                                                                    Sheets("Report15").Range("E18").Value = Sheets("Download").Range("M18").Value
                                                                    Sheets("Report15").Range("F24").Value = Sheets("Download").Range("AM18").Value
                                                                    Sheets("Report15").Range("F25").Value = Sheets("Download").Range("AN18").Value
                                                                    Sheets("Report15").Range("F26").Value = Sheets("Download").Range("AL18").Value
                                                                    Sheets("Report15").Range("F27").Value = Sheets("Download").Range("AO18").Value
                                                                    Sheets("Report15").Range("F28").Value = Sheets("Download").Range("AP18").Value
                                                                    Sheets("Report15").Range("F29").Value = Sheets("Download").Range("AQ18").Value
                                                                    Sheets("Report15").Range("A31").Value = Sheets("Download").Range("AJ18").Value
                                                                    Sheets("Report15").Range("B37").Value = Sheets("Download").Range("AD18").Value
                                                                    Sheets("Report15").Range("B38").Value = Sheets("Download").Range("AE18").Value
                                                                    Sheets("Report15").Range("B39").Value = Sheets("Download").Range("AF18").Value
                                                                    Sheets("Report15").Range("B40").Value = Sheets("Download").Range("AG18").Value
                                                                    Sheets("Report15").Range("B41").Value = Sheets("Download").Range("AH18").Value
                                                                    Sheets("Report15").Range("D37").Value = Sheets("Download").Range("Y18").Value
                                                                    Sheets("Report15").Range("D38").Value = Sheets("Download").Range("AA18").Value
                                                                    Sheets("Report15").Range("D39").Value = Sheets("Download").Range("Z18").Value
                                                                    Sheets("Report15").Range("D40").Value = Sheets("Download").Range("Ab18").Value
                                                                    Sheets("Report15").Range("D42").Value = Sheets("Download").Range("AI18").Value
                                                                    Sheets("Report15").Range("D49").Value = Sheets("Download").Range("AX18").Value
                                                                    Sheets("Report15").Range("A55").Value = Sheets("Download").Range("AK18").Value
    End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Hi,

I would use a helper sheet, for example sheet3:

Code: 
For each rng in Sheet3.Columns(1).specialcells(2,2)
   Sheets(rng.Value).Range(rng.offset(,1).Value).Value = Sheets("Download").Range(rng.offset(,2).Value).Value
next

in column A of that sheet, you write the sheet name (for instance Report15).
in column B of that sheet, you write the range address for the target (for instance A55).
in column C of that sheet, you write the range address for the source (for instance AK18).

You can suffice with 3 lines of code and a more maintainable solution (though not ideal).
 
Last edited:
Upvote 0
oldbrewer said:
what are you trying to do ?
Click to expand...
The code I have written basically takes input into "download" sheet and dependant on what row it is in puts it into relevant cells in "report" sheets.
For example entry into cell A4 in download sheet appears in cell B14 in Report1, cell A5 in download appears in cell B14 report2 etc.
Hope that clarifies
Regards
Oldbill
 
Upvote 0
I don't know the purpose of your Worksheet change event. Normally, one uses Intersect() to limit what "Changes" would require code execution. In any case, you should disable events most always when using those sorts of events.

As wigi said, when doing these sorts of things, using a helper column in a sheet does make maintenance easier. Though if you are the one that runs it, you can do it in code as I did. One does have to make sure that the array's one to one relationships are in sync and correct. Always test code on a backup copy of your file.

You can call Main() from your Change event if you like. I would put Main() into a Module.
Code: 
Sub Main()
  Dim i As Integer, j As Integer, ii() As String, jj() As String
  Dim glb_origCalculationMode As Integer
  
  'Some speed settings
  On Error GoTo EndSub
  glb_origCalculationMode = Application.Calculation
  With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Cursor = xlWait
    .StatusBar = "Running Main()"
    .EnableCancelKey = xlErrorHandler
  End With
  
  'Static cells for source
  ii() = Split("B14,B15,B16,B17,B18,B19,E8,E9,E14,E15,E16,E18," _
    & "F24,F25,F26,F27,F28,F29,A31,B37,B38,B39,B40,B41," _
    & "D37,D38,D39,D40,D42,D49,A55", ",")
  'Debug.Print Join(ii, vbLf), UBound(ii)
  
  'Column letter for target
  jj() = Split("A,B,C,E,J,L,K,O,E,F,H,M,AM,AN,Al,AO,AP,AQ,AJ,AD," _
    & "AE,AF,AG,AH,Y,AA,Z,AB,AI,AX,AK", ",")
  'Debug.Print vbCrLf
  'Debug.Print Join(jj, vbLf), UBound(jj)
  'GoTo EndSub
  
  For i = 1 To 15
    For j = 0 To UBound(jj)
      Worksheets("Report" & i).Range(ii(j)).Value = _
        Worksheets("Download").Range(jj(j) & i + 3).Value
    Next j
  Next i
  
EndSub:
  With Application
    .Calculation = glb_origCalculationMode
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    .CalculateBeforeSave = True
    .Cursor = xlDefault
    .StatusBar = False
    .EnableCancelKey = xlInterrupt
  End With
End Sub
 
Last edited:
Upvote 0
Kenneth Hobson said:
I don't know the purpose of your Worksheet change event. Normally, one uses Intersect() to limit what "Changes" would require code execution. In any case, you should disable events most always when using those sorts of events.

As wigi said, when doing these sorts of things, using a helper column in a sheet does make maintenance easier. Though if you are the one that runs it, you can do it in code as I did. One does have to make sure that the array's one to one relationships are in sync and correct. Always test code on a backup copy of your file.

You can call Main() from your Change event if you like. I would put Main() into a Module.
Code: 
Sub Main()
  Dim i As Integer, j As Integer, ii() As String, jj() As String
  Dim glb_origCalculationMode As Integer
  
  'Some speed settings
  On Error GoTo EndSub
  glb_origCalculationMode = Application.Calculation
  With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Cursor = xlWait
    .StatusBar = "Running Main()"
    .EnableCancelKey = xlErrorHandler
  End With
  
  'Static cells for source
  ii() = Split("B14,B15,B16,B17,B18,B19,E8,E9,E14,E15,E16,E18," _
    & "F24,F25,F26,F27,F28,F29,A31,B37,B38,B39,B40,B41," _
    & "D37,D38,D39,D40,D42,D49,A55", ",")
  'Debug.Print Join(ii, vbLf), UBound(ii)
  
  'Column letter for target
  jj() = Split("A,B,C,E,J,L,K,O,E,F,H,M,AM,AN,Al,AO,AP,AQ,AJ,AD," _
    & "AE,AF,AG,AH,Y,AA,Z,AB,AI,AX,AK", ",")
  'Debug.Print vbCrLf
  'Debug.Print Join(jj, vbLf), UBound(jj)
  'GoTo EndSub
  
  For i = 1 To 15
    For j = 0 To UBound(jj)
      Worksheets("Report" & i).Range(ii(j)).Value = _
        Worksheets("Download").Range(jj(j) & i + 3).Value
    Next j
  Next i
  
EndSub:
  With Application
    .Calculation = glb_origCalculationMode
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    .CalculateBeforeSave = True
    .Cursor = xlDefault
    .StatusBar = False
    .EnableCancelKey = xlInterrupt
  End With
End Sub
Click to expand...
Hi Keith
Thanks very much for your reply I'm afraid your explanation assumes far more skill and understanding on my part and 90% of it has gone straight over my head. Even though I have been trying to use VBA for sometime my understanding is still extremely sketchy. However I am desperate to learn and if you could perhaps explain how your code works in laymans terms I can progress a little instead of just blindly copying and pasting code.
I appreciate your patience
Regards
Oldbill
 
Upvote 0
For example entry into cell A4 in download sheet appears in cell B14 in Report1, cell A5 in download appears in cell B14 report2 etc.

so why not put in sheet report1 cell B14 =download!A4
and in report2 in cell B14 =download!A5

then drag down and across as needed - you only have 15 report sheets


 
Upvote 0
I explained how it works in the comments.

It uses one array to build the cell address of the source cells. The other array holds the column letters of the target cells. The for loops are based on seeing a pattern in your data. They concatenate and build sheet names and the source and target cells combined with the array values. So, all you have to maintain are the two arrays.

The beginning and end sections are just good practices to speedup performance. e.g. Application.EnableEvents = False, prevent code from executing a change event. Without it, any change will fire the whole change event section again. Screenupdating takes time too. If an error, it needs to restore the usual settings.

Press F1 in or next to a command work to get specific help.
 
Upvote 0
oldbrewer said:
For example entry into cell A4 in download sheet appears in cell B14 in Report1, cell A5 in download appears in cell B14 report2 etc.

so why not put in sheet report1 cell B14 =download!A4
and in report2 in cell B14 =download!A5

then drag down and across as needed - you only have 15 report sheets
Click to expand...
Hi Oldbrewer
Yes I had that as starting point but people kept overwriting formula
Regards
Oldbill
 
Upvote 0
The Debug.Print lines are what I use to check steps sometimes. I left them in for your information. The results are placed into the Visual Basic Editor's (VBE) Immediate Window after a run. You can delete those if you like.

Depending on your goal, I doubt that you really need all of that. If you are using a Change event, there should be a reason. e.g. Column B, rows 2 to end of column with data is changed, fire the Change event to say add one to that value into Column C of the same row. Most would limit the Change event code execution to say one value though many can be handled. e.g.
Code: 
If Target.Rows.Count >1 then Exit Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Forumla Error on Sum of lines Containing same Value
Replies
2
Views
231
jarett
J
S
  • Question
Duplicating tabs to new workbook but retaining data
Replies
1
Views
411
ajetrumpet
ajetrumpet
A
  • Question
Guys please help! Excel problem with index sort and filter function
Replies
2
Views
462
Adfassa
A
M
  • Solved
VBA Code to split week ranges into separate lines.
2
Replies
10
Views
2K
Joe4
Joe4
O
  • Question
Too many ifs
2
Replies
10
Views
863
Oldbill62
O

Forum statistics

Threads
1,214,979
Messages
6,122,552
Members
449,088
Latest member
davidcom

Share this page

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