any 'procedure too large' error tips?

[ebalboni]

I was writing a code and got it to work properly on a small scale and then spent a large amount of time making it work on a larger scale.... and then.... I got a 'procedure too large' error. I would ideally like to have everything under one command button, but I guess I can split my code up into other buttons, but it would not be as convenient for the user. Here is what my code would look like with 1 Tank case:

Private Sub CommandButton2_Click()

Dim myMonth As String
Dim myTank As String

myMonth = Worksheets("Input").Range("B6")

myTank = Worksheets("Input").Range("C6")

Application.ScreenUpdating = False

Select Case myTank
Case "1"
Select Case myMonth
Case "Jan"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K6").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D6").PasteSpecial _
Paste:=xlPasteValues
Case "Feb"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K7").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D7").PasteSpecial _
Paste:=xlPasteValues
Case "Mar"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K8").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D8").PasteSpecial _
Paste:=xlPasteValues
Case "Apr"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K9").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D9").PasteSpecial _
Paste:=xlPasteValues
Case "May"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K10").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D10").PasteSpecial _
Paste:=xlPasteValues
Case "Jun"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K11").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D11").PasteSpecial _
Paste:=xlPasteValues
Case "Jul"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K12").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D12").PasteSpecial _
Paste:=xlPasteValues
Case "Aug"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K13").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D13").PasteSpecial _
Paste:=xlPasteValues
Case "Sep"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K14").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D14").PasteSpecial _
Paste:=xlPasteValues
Case "Oct"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K15").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D15").PasteSpecial _
Paste:=xlPasteValues
Case "Nov"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K16").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D16").PasteSpecial _
Paste:=xlPasteValues
Case "Dec"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K17").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D17").PasteSpecial _
Paste:=xlPasteValues
End Select
End Select

Application.ScreenUpdating = True

MsgBox "Input Successful"

End Sub

​

Now, I have 75 Tanks that I would like to do this copy paste operation for. Is there any way I can bring it to a manageable size and keep it on one button?

 

[Norie]

Why are you repeating what is basically the same code 12 times?

The only thing that appears to be changing is the range you are pasting to.

Private Sub CommandButton2_Click()
Dim myMonth As String
Dim myTank As String
    myMonth = Worksheets("Input").Range("B6")
    
    myTank = Worksheets("Input").Range("C6")
    
    Application.ScreenUpdating = False
    
    Select Case myTank
        Case "1"
            x = Month("1/" & myMonth & "/2008") - 1
    
        Worksheets("Tankcalculations").Range("E17:AJ17").Copy
        Worksheets("Output").Range("K6").Offset(x).PasteSpecial Paste:=xlPasteValues
        Worksheets("Input").Range("B6:H6").Copy
        Worksheets("Output").Range("D6").Offset(x).PasteSpecial Paste:=xlPasteValues
    End Select
    
    Application.ScreenUpdating = True
    
    MsgBox "Input Successful"
End Sub

 

[ebalboni]

excellent, thank you that was very helpful... would you mind helping me from repeating my tanks 75 times? Now my tank names change by uneven amounts (i.e. they are 1, 3, 4, 5, 6, 7, 9, etc.). So how can I repeat the case 75 times but varying the tank number? Here is a sample of what I would be doing:

Private Sub CommandButton6_Click()
Dim myMonth As String
Dim myTank As String
myMonth = Worksheets("Input").Range("B6")

myTank = Worksheets("Input").Range("C6")

Application.ScreenUpdating = False

Select Case myTank
Case "1"
x = Month("1/" & myMonth & "/2008") - 1

Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K6").Offset(x).PasteSpecial Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D6").Offset(x).PasteSpecial Paste:=xlPasteValues
Case "3"
x = Month("1/" & myMonth & "/2008") - 1

Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K18").Offset(x).PasteSpecial Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D18").Offset(x).PasteSpecial Paste:=xlPasteValues
Case "4"
x = Month("1/" & myMonth & "/2008") - 1

Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K30").Offset(x).PasteSpecial Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D30").Offset(x).PasteSpecial Paste:=xlPasteValues
End Select

Application.ScreenUpdating = True

MsgBox "Input Successful"
End Sub

 

[Norie]

Do you have a list of the tank numbers anywhere?

 

[ebalboni]

yes:

1,3,4,5,6,7,9,10,11,16,17,18,20,21,23,24,25,30,33,40,41,42,48,49,50,51,52,55,56,57,58,59,70,71,72,73,82,83,85,88,89,100,126,127,300,301,302,303,304,305,351,352,360,361,362,363,364,377,378,381,382,383,408,623,801,802,803,804,805,806,808,1M,4N,5N,6R

 

[Norie]

And are you storing this list anywhere? eg on a worksheet

The reason I'm asking is because again I see that you've got repetive code.

And you just seem to be offsetting 12 rows between tanks.

So it could be possible, if there is a list of tanks, to locate the position of the tank in the list and use that for another offset in the code.

 

[ebalboni]

yes i have the list in a column (B4:B78) in the 'validation' sheet in my workbook.

 

[ebalboni]

how would I reference the list this way?