Splitting Procedures

[Markus]

When I run the code below I get a compile error message
that states procedure to large. The knowledge base tells
me to split the procedure. How do i do this? Here is the
procedure below. Any suggestions or help is extremely
appreciated. This code is repeated until Case 14 where
only the Worksheet name changes "QA1"-"QA15". How can I
split it and still update the cells when the Up1 command
button is clicked?

Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item in list
Range("C5").Value = Worksheets("QA1").Range
("B4").Value
Range("C6").Value = Worksheets("QA1").Range
("E4").Value
Range("C13").Value = Worksheets("QA1").Range
("D6").Value
Range("D13").Value = Worksheets("QA1").Range
("E6").Value
Range("E13").Value = Worksheets("QA1").Range
("F6").Value
Range("F13").Value = Worksheets("QA1").Range
("G6").Value
Range("G13").Value = Worksheets("QA1").Range
("H6").Value
Range("H13").Value = Worksheets("QA1").Range
("I6").Value
Range("I13").Value = Worksheets("QA1").Range
("J6").Value
Range("J13").Value = Worksheets("QA1").Range
("K6").Value
Range("K13").Value = Worksheets("QA1").Range
("L6").Value
Range("L13").Value = Worksheets("QA1").Range
("M6").Value
Range("M13").Value = Worksheets("QA1").Range
("N6").Value
Range("N13").Value = Worksheets("QA1").Range
("O6").Value
Range("C14").Value = Worksheets("QA1").Range
("D9").Value
Range("D14").Value = Worksheets("QA1").Range
("E9").Value
Range("E14").Value = Worksheets("QA1").Range
("F9").Value
Range("F14").Value = Worksheets("QA1").Range
("G9").Value
Range("G14").Value = Worksheets("QA1").Range
("H9").Value
Range("H14").Value = Worksheets("QA1").Range
("I9").Value
Range("I14").Value = Worksheets("QA1").Range
("J9").Value
Range("J14").Value = Worksheets("QA1").Range
("K9").Value
Range("K14").Value = Worksheets("QA1").Range
("L9").Value
Range("L14").Value = Worksheets("QA1").Range
("M9").Value
Range("M14").Value = Worksheets("QA1").Range
("N9").Value
Range("N14").Value = Worksheets("QA1").Range
("O9").Value
Range("C31").Value = Worksheets("QA1").Range
("D11").Value
Range("D31").Value = Worksheets("QA1").Range
("E11").Value
Range("E31").Value = Worksheets("QA1").Range
("F11").Value
Range("F31").Value = Worksheets("QA1").Range
("G11").Value
Range("G31").Value = Worksheets("QA1").Range
("H11").Value
Range("H31").Value = Worksheets("QA1").Range
("I11").Value
Range("I31").Value = Worksheets("QA1").Range
("J11").Value
Range("J31").Value = Worksheets("QA1").Range
("K11").Value
Range("K31").Value = Worksheets("QA1").Range
("L11").Value
Range("L31").Value = Worksheets("QA1").Range
("M11").Value
Range("M31").Value = Worksheets("QA1").Range
("N11").Value
Range("N31").Value = Worksheets("QA1").Range
("O11").Value
Range("C49").Value = Worksheets("QA1").Range
("D12").Value
Range("D49").Value = Worksheets("QA1").Range
("E12").Value
Range("E49").Value = Worksheets("QA1").Range
("F12").Value
Range("F49").Value = Worksheets("QA1").Range
("G12").Value
Range("G49").Value = Worksheets("QA1").Range
("H12").Value
Range("H49").Value = Worksheets("QA1").Range
("I12").Value
Range("I49").Value = Worksheets("QA1").Range
("J12").Value
Range("J49").Value = Worksheets("QA1").Range
("K12").Value
Range("K49").Value = Worksheets("QA1").Range
("L12").Value
Range("L49").Value = Worksheets("QA1").Range
("M12").Value
Range("M49").Value = Worksheets("QA1").Range
("N12").Value
Range("N49").Value = Worksheets("QA1").Range
("O12").Value
Range("C78").Value = Worksheets("QA1").Range
("D13").Value
Range("D78").Value = Worksheets("QA1").Range
("E13").Value
Range("E78").Value = Worksheets("QA1").Range
("F13").Value
Range("F78").Value = Worksheets("QA1").Range
("G13").Value
Range("G78").Value = Worksheets("QA1").Range
("H13").Value
Range("H78").Value = Worksheets("QA1").Range
("I13").Value
Range("I78").Value = Worksheets("QA1").Range
("J13").Value
Range("J78").Value = Worksheets("QA1").Range
("K13").Value
Range("K78").Value = Worksheets("QA1").Range
("L13").Value
Range("L78").Value = Worksheets("QA1").Range
("M13").Value
Range("M78").Value = Worksheets("QA1").Range
("N13").Value
Range("N78").Value = Worksheets("QA1").Range
("O13").Value
Range("C96").Value = Worksheets("QA1").Range
("D14").Value
Range("D96").Value = Worksheets("QA1").Range
("E14").Value
Range("E96").Value = Worksheets("QA1").Range
("F14").Value
Range("F96").Value = Worksheets("QA1").Range
("G14").Value
Range("G96").Value = Worksheets("QA1").Range
("H14").Value
Range("H96").Value = Worksheets("QA1").Range
("I14").Value
Range("I96").Value = Worksheets("QA1").Range
("J14").Value
Range("J96").Value = Worksheets("QA1").Range
("K14").Value
Range("K96").Value = Worksheets("QA1").Range
("L14").Value
Range("L96").Value = Worksheets("QA1").Range
("M14").Value
Range("M96").Value = Worksheets("QA1").Range
("N14").Value
Range("N96").Value = Worksheets("QA1").Range
("O14").Value
Range("C114").Value = Worksheets("QA1").Range
("D16").Value
Range("D114").Value = Worksheets("QA1").Range
("E16").Value
Range("E114").Value = Worksheets("QA1").Range
("F16").Value
Range("F114").Value = Worksheets("QA1").Range
("G16").Value
Range("G114").Value = Worksheets("QA1").Range
("H16").Value
Range("H114").Value = Worksheets("QA1").Range
("I16").Value
Range("I114").Value = Worksheets("QA1").Range
("J16").Value
Range("J114").Value = Worksheets("QA1").Range
("K16").Value
Range("K114").Value = Worksheets("QA1").Range
("L16").Value
Range("L114").Value = Worksheets("QA1").Range
("M16").Value
Range("M114").Value = Worksheets("QA1").Range
("N16").Value
Range("N114").Value = Worksheets("QA1").Range
("O16").Value
End Select
End Sub

 

[John Wilson]

Mark,

See your previous post for some other suggestions.
Please don't post duplicate questions in the ng if you
don't get an answer right away to the first one.
Be patient. If you're question is answerable (and most are)
we'll eventually get to it.

John

 

[Myrna Larson]

You need to learn about loops!

1. If the only difference between the Select Case blocks is the sheet name, you can construct
the sheet name from the ListIndex, i.e.

SheetName = "QA" & LB1.ListIndex + 1

and you don't need a select case block at all.

2. You can replace the 12 statements that set C13:N13 with data from D6:O6 with one:

Range("C13:N13").Value = Worksheets(SheetName).Range("D6:O6").Value

Similarly

Range("C14:N14").Value = worksheets(SheetName).Range("D9:09").Value

3. But you can copy the rows more efficiently with a For/Next loop.

Now you won't have any problem with the size of your code <vbg>.

Please let me know if the following does what you want.

BTW, if you really wanted to split the procedure, you would have to create 15 different subs, in
2 or 3 modules, and in your Click event procedure you would call the right routine based on the
list index. So the Select Case block would look like

Select Case LB1.List.Index
Case 0: DoSheetQA1
Case 1: DoSheetQA2
etc.

But 15 subs ISN'T the right way to do this!

Here's the code:

Private Sub Up1_Click()
Dim QA As Worksheet
Dim SourceRows As Variant
Dim DestRows As Variant
Dim I As Long

Set QA = Worksheets("QA" & LB1.ListIndex + 1)

SourceRows = Array(6, 9, 11, 12, 13, 14, 16) 'check that I have these right!
DestRows = Array(13, 14, 31, 49, 78, 96, 114)

Range("C5").Value = QA.Range("B4").Value
Range("C6").Value = QA.Range("E4").Value

For I = LBound(SourceRows) To Ubound(SourceRows)
'copy all 12 cells from source row column D
'to destination row column C
Cells(DestRows(I), "C").Resize(1, 12).Value = _
QA.Cells(SourceRows(I), "D").Resize(1, 12).Value
Next I
End Sub

 

[Tom Ogilvy]

for i = 0 to 7
should be

for i = 0 to 6

or

for i = lbound(varr) to ubound(varr)

 

[Markus]

I apologize for that...the first time i posted the message
i placed the entire code in there and the post would not
pull up on my PC. So i just re-entered the post again.