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:
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:
Code:
[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]