Compile Error - Procedure too large - How do I reduce my code?

[Deemon]

I need help with a database I'm setting up. I have one excel spreadsheet where the data is entered, and this is then submitted onto a second excel spreadsheet which is kept in a different location, and apply formulas that exist in the datastore location to the new information. I am trying to get the information to submit onto the second spreadsheet but I'm coming up with the compile error because my procedure is too large.

I need to submit information for 37 Trainees so I have the code repeated 37 times - There has to be an easier way to do this - but based on my limited knowledge a loop won't work because for each Trainee there are different textboxes etc on my user form. (Please note - this query has also been posted on Excel Key forum) The code looks something like this:

[FONT=Courier New]Private Sub CommandButton1_Click()
Dim ExBook As Workbook, ExApp As Application
Set ExApp = New Excel.Application
Set ExBook = ExApp.Workbooks.Open(Data 2014.xls")

If Trainee1.Value = "" Then GoTo Closefunc

ExBook.Application.Sheets("Lates").Cells(2, 1) = Trainee1.Value
ExBook.Application.Sheets("Lates").Cells(2, 5) = TextBox35.Value
ExBook.Application.Sheets("Lates").Cells(2, 3) = GROUP1.Value
ExBook.Application.Sheets("Lates").Cells(2, 8) = Environ("Username")
ExBook.Application.Sheets("Lates").Cells(2, 12) = TextBox46.Value
ExBook.Application.Sheets("Lates").Cells(2, 11) = TextBox3.Value


If Late1.Value = True Then ExBook.Application.Sheets("Lates").Cells(2, 6) = cbLate1.Value
If Absent1.Value = True Then ExBook.Application.Sheets("Lates").Cells(2, 6) = cbAbsent1.Value
If Absent1.Value = False Then If Late1.Value = False Then ExBook.Application.Sheets("Lates").Cells(2, 6) = "N/A"

ExBook.Application.Sheets("Lates").Activate
ExBook.Application.Range("A2").Select
ExBook.Application.Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ExBook.Application.Sheets("Lates").Cells(2, 5) = ExBook.Application.Sheets("Lates").Cells(1, 5).FormulaR1C1
ExBook.Application.Sheets("Lates").Cells(2, 7) = ExBook.Application.Sheets("Lates").Cells(1, 7).FormulaR1C1
ExBook.Application.Sheets("Lates").Cells(2, 5) = ExBook.Application.Sheets("Lates").Cells(1, 5).FormulaR1C1
ExBook.Application.Sheets("Lates").Cells(2, 9) = ExBook.Application.Sheets("Lates").Cells(1, 9).FormulaR1C1


If Trainee2.Value = "" Then GoTo Closefunc
ExBook.Application.Sheets("Lates").Cells(2, 1) = Trainee2.Value
ExBook.Application.Sheets("Lates").Cells(2, 5) = TextBox35.Value
ExBook.Application.Sheets("Lates").Cells(2, 3) = GROUP1.Value
ExBook.Application.Sheets("Lates").Cells(2, 8) = Environ("Username")
ExBook.Application.Sheets("Lates").Cells(2, 12) = TextBox47.Value
ExBook.Application.Sheets("Lates").Cells(2, 11) = TextBox4.Value

If TextBox2.Value = "Training Group" Then ExBook.Application.Sheets("Lates").Cells(2, 3) = GROUP1.Value
 
If Late2.Value = True Then ExBook.Application.Sheets("Lates").Cells(2, 6) = cbLate2.Value
If Absent2.Value = True Then ExBook.Application.Sheets("Lates").Cells(2, 6) = cbAbsent2.Value
If Absent2.Value = False Then If Late2.Value = False Then ExBook.Application.Sheets("Lates").Cells(2, 6) = "N/A"

ExBook.Application.Sheets("Lates").Activate
ExBook.Application.Range("A2").Select
ExBook.Application.Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ExBook.Application.Sheets("Lates").Cells(2, 5) = ExBook.Application.Sheets("Lates").Cells(1, 5).FormulaR1C1
ExBook.Application.Sheets("Lates").Cells(2, 7) = ExBook.Application.Sheets("Lates").Cells(1, 7).FormulaR1C1
ExBook.Application.Sheets("Lates").Cells(2, 9) = ExBook.Application.Sheets("Lates").Cells(1, 9).FormulaR1C1
ExBook.Application.Sheets("Lates").Cells(2, 5) = ExBook.Application.Sheets("Lates").Cells(1, 5).FormulaR1C1[/FONT]

 

[j_harr]

I would recommend reading up on With statements, when you're doing repeated operations in the same place they can help reduce a lot of your code. Microsoft website link: With...End With Statement (Visual Basic)
I also created another variable named ExWs which refers to your Lates sheet to help condese some more. Your code can be reduced further if needed, this was just the quick and easy stuff.

Private Sub CommandButton1_Click()
Dim ExBook As Workbook, ExApp As Application, ExWs As Worksheet
Set ExApp = New Excel.Application
Set ExBook = ExApp.Workbooks.Open(Data 2014.xls")
Set ExWs = Sheets("Lates")
If Trainee1.Value = "" Then GoTo Closefunc
With ExWs
    .Cells(2, 1) = Trainee1.Value
    .Cells(2, 5) = TextBox35.Value
    .Cells(2, 3) = GROUP1.Value
    .Cells(2, 8) = Environ("Username")
    .Cells(2, 12) = TextBox46.Value
    .Cells(2, 11) = TextBox3.Value
End With
If Late1.Value = True Then ExWs.Cells(2, 6) = cbLate1.Value
If Absent1.Value = True Then ExWs.Cells(2, 6) = cbAbsent1.Value
If Absent1.Value = False Then If Late1.Value = False Then ExWs.Cells(2, 6) = "N/A"
ExWs.Range("A2").Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
With ExWs
    .Cells(2, 5) = ExWs.Cells(1, 5).FormulaR1C1
    .Cells(2, 7) = ExWs.Cells(1, 7).FormulaR1C1
    .Cells(2, 5) = ExWs.Cells(1, 5).FormulaR1C1
    .Cells(2, 9) = ExWs.Cells(1, 9).FormulaR1C1
End With
If Trainee2.Value = "" Then GoTo Closefunc
With ExWs
    .Cells(2, 1) = Trainee2.Value
    .Cells(2, 5) = TextBox35.Value
    .Cells(2, 3) = GROUP1.Value
    .Cells(2, 8) = Environ("Username")
    .Cells(2, 12) = TextBox47.Value
    .Cells(2, 11) = TextBox4.Value
End With
If TextBox2.Value = "Training Group" Then ExWs.Cells(2, 3) = GROUP1.Value
 
If Late2.Value = True Then ExWs.Cells(2, 6) = cbLate2.Value
If Absent2.Value = True Then ExWs.Cells(2, 6) = cbAbsent2.Value
If Absent2.Value = False Then If Late2.Value = False Then ExWs.Cells(2, 6) = "N/A"
ExWs.Range("A2").Select
ExBook.Application.Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
With ExWs
    .Cells(2, 5) = ExWs.Cells(1, 5).FormulaR1C1
    .Cells(2, 7) = ExWs.Cells(1, 7).FormulaR1C1
    .Cells(2, 9) = ExWs.Cells(1, 9).FormulaR1C1
    .Cells(2, 5) = ExWs.Cells(1, 5).FormulaR1C1
End With
End Sub

As for your code on a higher level, you could stick it all in a loop and keep loading the form and pasting in the data they enter until they've entered enough. Take this process:

display userform
user enters data
user clicks CommandButton1
above code pastes in data

and loop it until they've entered all the trainees. This can be done with a Do... Loop statement or a For... Next statment. They can be a bit tricky to learn at first so fire away with any questions you've got:
Links:
Do...Loop Statement (Visual Basic)
For...Next Statement (Visual Basic)