any 'procedure too large' error tips?

ebalboni

New Member
Joined
Jul 21, 2008
Messages
18
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:
Code:
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?
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Jul 21, 2008
Messages
18
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Do you have a list of the tank numbers anywhere?
 

ebalboni

New Member
Joined
Jul 21, 2008
Messages
18

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 21, 2008
Messages
18
yes i have the list in a column (B4:B78) in the 'validation' sheet in my workbook.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,691
Messages
5,597,562
Members
414,156
Latest member
WDMix

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
Top