J
Jennifer Glass
Hi,
I am trying to put some code on a command button that will
move data depending on what value is chosen from in a list
box. Problem is it's alot of data and I've only written
one case and it's really long. I need 12 cases in all and
that would make it too long and hard to create. I'm pretty
new to visual basic and I've heard of looping and I don't
know if I fully understand it yet. Could anyone help me?
Basically what I need is to create the following case 11
more times. The destination cells columns rows and ranges
will be the same but the source rows should increase by 40
rows. Everything else stays the same. Here the first case
in my code....Can anyone help me with what my code should
look like?
Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Worksheets("MRep1").Range("P28:U28").Value =
Worksheets("Evaluation").Range("B17:G17").Value
Worksheets("MRep1").Range("P27:U27").Value =
Worksheets("Evaluation").Range("B25:G25").Value
Worksheets("MRep1").Range("P29:U29").Value =
Worksheets("Evaluation").Range("B26:G26").Value
Worksheets("MRep1").Range("D28:I28").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep1").Range("D27:I27").Value =
Worksheets("Evaluation").Range("B23:G23").Value
Worksheets("MRep1").Range("D29:I29").Value =
Worksheets("Evaluation").Range("B24:G24").Value
Worksheets("MRep1").Range("J54:O54").Value =
Worksheets("Evaluation").Range("B7:G7").Value
Worksheets("MRep1").Range("J55:O55").Value =
Worksheets("Evaluation").Range("B12:G12").Value
Worksheets("MRep1").Range("J56:O56").Value =
Worksheets("Evaluation").Range("B27:G27").Value
Worksheets("MRep1").Range("D55").Value =
Worksheets("Evaluation").Range("G13").Value
Worksheets("MRep1").Range("E55").Value =
Worksheets("Evaluation").Range("G8").Value
Worksheets("MRep1").Range("S54").Value =
Worksheets("Evaluation").Range("I5").Value
Worksheets("MRep1").Range("T54").Value =
Worksheets("Evaluation").Range("N5").Value
Worksheets("MRep1").Range("U54").Value =
Worksheets("Evaluation").Range("S5").Value
Worksheets("MRep1").Range("V54").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep1").Range("W54").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep1").Range("X54").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep1").Range("S55").Value =
Worksheets("Evaluation").Range("I7").Value
Worksheets("MRep1").Range("T55").Value =
Worksheets("Evaluation").Range("N7").Value
Worksheets("MRep1").Range("U55").Value =
Worksheets("Evaluation").Range("S7").Value
Worksheets("MRep1").Range("V55").Value =
Worksheets("Evaluation").Range("I12").Value
Worksheets("MRep1").Range("W55").Value =
Worksheets("Evaluation").Range("N12").Value
Worksheets("MRep1").Range("X55").Value =
Worksheets("Evaluation").Range("S12").Value
Worksheets("MRep1").Range("S56").Value =
Worksheets("Evaluation").Range("I8").Value
Worksheets("MRep1").Range("T56").Value =
Worksheets("Evaluation").Range("N8").Value
Worksheets("MRep1").Range("U56").Value =
Worksheets("Evaluation").Range("S8").Value
Worksheets("MRep1").Range("V56").Value =
Worksheets("Evaluation").Range("I13").Value
Worksheets("MRep1").Range("W56").Value =
Worksheets("Evaluation").Range("N13").Value
Worksheets("MRep1").Range("X56").Value =
Worksheets("Evaluation").Range("S13").Value
Worksheets("MRep2").Range("D52:I52").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep2").Range("D53:I53").Value =
Worksheets("Evaluation").Range("B28:G28").Value
Worksheets("MRep2").Range("D54:I54").Value =
Worksheets("Evaluation").Range("B29:G29").Value
Worksheets("MRep2").Range("P52:U52").Value =
Worksheets("Evaluation").Range("B30:G30").Value
Worksheets("MRep2").Range("P53:U53").Value =
Worksheets("Evaluation").Range("B31:G31").Value
Worksheets("MRep2").Range("F28").Value =
Worksheets("Evaluation").Range("B33").Value
Worksheets("MRep2").Range("G28").Value =
Worksheets("Evaluation").Range("B34").Value
Worksheets("MRep2").Range("F29").Value =
Worksheets("Evaluation").Range("B35").Value
Worksheets("MRep2").Range("G29").Value =
Worksheets("Evaluation").Range("B36").Value
Worksheets("MRep2").Range("R26").Value =
Worksheets("Evaluation").Range("F33").Value
Worksheets("MRep2").Range("R27").Value =
Worksheets("Evaluation").Range("F34").Value
Worksheets("MRep2").Range("R28").Value =
Worksheets("Evaluation").Range("F35").Value
Worksheets("MRep2").Range("R29").Value =
Worksheets("Evaluation").Range("F36").Value
Worksheets("MRep3").Range("B11").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep3").Range("B19").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep3").Range("B27").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep3").Range("F11").Value =
Worksheets("Evaluation").Range("J14").Value
Worksheets("MRep3").Range("F19").Value =
Worksheets("Evaluation").Range("O14").Value
Worksheets("MRep3").Range("F27").Value =
Worksheets("Evaluation").Range("T14").Value
Worksheets("MRep3").Range("H11").Value =
Worksheets("Evaluation").Range("H16").Value
Worksheets("MRep3").Range("H19").Value =
Worksheets("Evaluation").Range("M16").Value
Worksheets("MRep3").Range("H27").Value =
Worksheets("Evaluation").Range("R16").Value
Worksheets("MRep3").Range("M11").Value =
Worksheets("Evaluation").Range("H21").Value
Worksheets("MRep3").Range("M19").Value =
Worksheets("Evaluation").Range("M21").Value
Worksheets("MRep3").Range("M27").Value =
Worksheets("Evaluation").Range("R21").Value
Worksheets("MRep3").Range("B37").Value =
Worksheets("Evaluation").Range("I26").Value
Worksheets("MRep3").Range("F37").Value =
Worksheets("Evaluation").Range("J30").Value
Worksheets("MRep3").Range("H37").Value =
Worksheets("Evaluation").Range("H32").Value
Worksheets("MRep3").Range("M37").Value =
Worksheets("Evaluation").Range("H37").Value
Worksheets("MRep3").Range("F46").Value =
Worksheets("Evaluation").Range("O30").Value
Worksheets("MRep3").Range("H46").Value =
Worksheets("Evaluation").Range("M32").Value
Worksheets("MRep3").Range("M46").Value =
Worksheets("Evaluation").Range("M37").Value
Worksheets("MRep3").Range("B15:E15").Value =
Worksheets("Evaluation").Range("I12:L12").Value
Worksheets("MRep3").Range("B16:E16").Value =
Worksheets("Evaluation").Range("I13:L13").Value
Worksheets("MRep3").Range("B23:E23").Value =
Worksheets("Evaluation").Range("N12:Q12").Value
Worksheets("MRep3").Range("B24:E24").Value =
Worksheets("Evaluation").Range("N13:Q13").Value
Worksheets("MRep3").Range("B31:E31").Value =
Worksheets("Evaluation").Range("S12:V12").Value
Worksheets("MRep3").Range("B32:E32").Value =
Worksheets("Evaluation").Range("S13:V13").Value
Worksheets("MRep3").Range("B41:E41").Value =
Worksheets("Evaluation").Range("I28:L28").Value
Worksheets("MRep3").Range("B49:E49").Value =
Worksheets("Evaluation").Range("N28:Q28").Value
Worksheets("MRep3").Range("B50:E50").Value =
Worksheets("Evaluation").Range("N29:Q29").Value
Worksheets("MRep3").Range("A15").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A23").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A31").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A41").Value =
Worksheets("Evaluation").Range("I29").Value
Worksheets("MRep4").Range("A9").Value =
Worksheets("Evaluation").Range("W5").Value
Worksheets("MRep4").Range("A16").Value =
Worksheets("Evaluation").Range("W11").Value
Worksheets("MRep4").Range("A23").Value =
Worksheets("Evaluation").Range("W17").Value
Worksheets("MRep4").Range("A30").Value =
Worksheets("Evaluation").Range("W23").Value
Worksheets("MRep4").Range("A37").Value =
Worksheets("Evaluation").Range("W29").Value
End Select
End Sub
I am trying to put some code on a command button that will
move data depending on what value is chosen from in a list
box. Problem is it's alot of data and I've only written
one case and it's really long. I need 12 cases in all and
that would make it too long and hard to create. I'm pretty
new to visual basic and I've heard of looping and I don't
know if I fully understand it yet. Could anyone help me?
Basically what I need is to create the following case 11
more times. The destination cells columns rows and ranges
will be the same but the source rows should increase by 40
rows. Everything else stays the same. Here the first case
in my code....Can anyone help me with what my code should
look like?
Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Worksheets("MRep1").Range("P28:U28").Value =
Worksheets("Evaluation").Range("B17:G17").Value
Worksheets("MRep1").Range("P27:U27").Value =
Worksheets("Evaluation").Range("B25:G25").Value
Worksheets("MRep1").Range("P29:U29").Value =
Worksheets("Evaluation").Range("B26:G26").Value
Worksheets("MRep1").Range("D28:I28").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep1").Range("D27:I27").Value =
Worksheets("Evaluation").Range("B23:G23").Value
Worksheets("MRep1").Range("D29:I29").Value =
Worksheets("Evaluation").Range("B24:G24").Value
Worksheets("MRep1").Range("J54:O54").Value =
Worksheets("Evaluation").Range("B7:G7").Value
Worksheets("MRep1").Range("J55:O55").Value =
Worksheets("Evaluation").Range("B12:G12").Value
Worksheets("MRep1").Range("J56:O56").Value =
Worksheets("Evaluation").Range("B27:G27").Value
Worksheets("MRep1").Range("D55").Value =
Worksheets("Evaluation").Range("G13").Value
Worksheets("MRep1").Range("E55").Value =
Worksheets("Evaluation").Range("G8").Value
Worksheets("MRep1").Range("S54").Value =
Worksheets("Evaluation").Range("I5").Value
Worksheets("MRep1").Range("T54").Value =
Worksheets("Evaluation").Range("N5").Value
Worksheets("MRep1").Range("U54").Value =
Worksheets("Evaluation").Range("S5").Value
Worksheets("MRep1").Range("V54").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep1").Range("W54").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep1").Range("X54").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep1").Range("S55").Value =
Worksheets("Evaluation").Range("I7").Value
Worksheets("MRep1").Range("T55").Value =
Worksheets("Evaluation").Range("N7").Value
Worksheets("MRep1").Range("U55").Value =
Worksheets("Evaluation").Range("S7").Value
Worksheets("MRep1").Range("V55").Value =
Worksheets("Evaluation").Range("I12").Value
Worksheets("MRep1").Range("W55").Value =
Worksheets("Evaluation").Range("N12").Value
Worksheets("MRep1").Range("X55").Value =
Worksheets("Evaluation").Range("S12").Value
Worksheets("MRep1").Range("S56").Value =
Worksheets("Evaluation").Range("I8").Value
Worksheets("MRep1").Range("T56").Value =
Worksheets("Evaluation").Range("N8").Value
Worksheets("MRep1").Range("U56").Value =
Worksheets("Evaluation").Range("S8").Value
Worksheets("MRep1").Range("V56").Value =
Worksheets("Evaluation").Range("I13").Value
Worksheets("MRep1").Range("W56").Value =
Worksheets("Evaluation").Range("N13").Value
Worksheets("MRep1").Range("X56").Value =
Worksheets("Evaluation").Range("S13").Value
Worksheets("MRep2").Range("D52:I52").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep2").Range("D53:I53").Value =
Worksheets("Evaluation").Range("B28:G28").Value
Worksheets("MRep2").Range("D54:I54").Value =
Worksheets("Evaluation").Range("B29:G29").Value
Worksheets("MRep2").Range("P52:U52").Value =
Worksheets("Evaluation").Range("B30:G30").Value
Worksheets("MRep2").Range("P53:U53").Value =
Worksheets("Evaluation").Range("B31:G31").Value
Worksheets("MRep2").Range("F28").Value =
Worksheets("Evaluation").Range("B33").Value
Worksheets("MRep2").Range("G28").Value =
Worksheets("Evaluation").Range("B34").Value
Worksheets("MRep2").Range("F29").Value =
Worksheets("Evaluation").Range("B35").Value
Worksheets("MRep2").Range("G29").Value =
Worksheets("Evaluation").Range("B36").Value
Worksheets("MRep2").Range("R26").Value =
Worksheets("Evaluation").Range("F33").Value
Worksheets("MRep2").Range("R27").Value =
Worksheets("Evaluation").Range("F34").Value
Worksheets("MRep2").Range("R28").Value =
Worksheets("Evaluation").Range("F35").Value
Worksheets("MRep2").Range("R29").Value =
Worksheets("Evaluation").Range("F36").Value
Worksheets("MRep3").Range("B11").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep3").Range("B19").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep3").Range("B27").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep3").Range("F11").Value =
Worksheets("Evaluation").Range("J14").Value
Worksheets("MRep3").Range("F19").Value =
Worksheets("Evaluation").Range("O14").Value
Worksheets("MRep3").Range("F27").Value =
Worksheets("Evaluation").Range("T14").Value
Worksheets("MRep3").Range("H11").Value =
Worksheets("Evaluation").Range("H16").Value
Worksheets("MRep3").Range("H19").Value =
Worksheets("Evaluation").Range("M16").Value
Worksheets("MRep3").Range("H27").Value =
Worksheets("Evaluation").Range("R16").Value
Worksheets("MRep3").Range("M11").Value =
Worksheets("Evaluation").Range("H21").Value
Worksheets("MRep3").Range("M19").Value =
Worksheets("Evaluation").Range("M21").Value
Worksheets("MRep3").Range("M27").Value =
Worksheets("Evaluation").Range("R21").Value
Worksheets("MRep3").Range("B37").Value =
Worksheets("Evaluation").Range("I26").Value
Worksheets("MRep3").Range("F37").Value =
Worksheets("Evaluation").Range("J30").Value
Worksheets("MRep3").Range("H37").Value =
Worksheets("Evaluation").Range("H32").Value
Worksheets("MRep3").Range("M37").Value =
Worksheets("Evaluation").Range("H37").Value
Worksheets("MRep3").Range("F46").Value =
Worksheets("Evaluation").Range("O30").Value
Worksheets("MRep3").Range("H46").Value =
Worksheets("Evaluation").Range("M32").Value
Worksheets("MRep3").Range("M46").Value =
Worksheets("Evaluation").Range("M37").Value
Worksheets("MRep3").Range("B15:E15").Value =
Worksheets("Evaluation").Range("I12:L12").Value
Worksheets("MRep3").Range("B16:E16").Value =
Worksheets("Evaluation").Range("I13:L13").Value
Worksheets("MRep3").Range("B23:E23").Value =
Worksheets("Evaluation").Range("N12:Q12").Value
Worksheets("MRep3").Range("B24:E24").Value =
Worksheets("Evaluation").Range("N13:Q13").Value
Worksheets("MRep3").Range("B31:E31").Value =
Worksheets("Evaluation").Range("S12:V12").Value
Worksheets("MRep3").Range("B32:E32").Value =
Worksheets("Evaluation").Range("S13:V13").Value
Worksheets("MRep3").Range("B41:E41").Value =
Worksheets("Evaluation").Range("I28:L28").Value
Worksheets("MRep3").Range("B49:E49").Value =
Worksheets("Evaluation").Range("N28:Q28").Value
Worksheets("MRep3").Range("B50:E50").Value =
Worksheets("Evaluation").Range("N29:Q29").Value
Worksheets("MRep3").Range("A15").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A23").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A31").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A41").Value =
Worksheets("Evaluation").Range("I29").Value
Worksheets("MRep4").Range("A9").Value =
Worksheets("Evaluation").Range("W5").Value
Worksheets("MRep4").Range("A16").Value =
Worksheets("Evaluation").Range("W11").Value
Worksheets("MRep4").Range("A23").Value =
Worksheets("Evaluation").Range("W17").Value
Worksheets("MRep4").Range("A30").Value =
Worksheets("Evaluation").Range("W23").Value
Worksheets("MRep4").Range("A37").Value =
Worksheets("Evaluation").Range("W29").Value
End Select
End Sub