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:
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?
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
Last edited by a moderator: