Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim g As Integer
Dim e As Integer
Dim h As Integer
Dim Lastrow As Long
Dim d As Date
Dim ctl As MSForms.Control
'Select Plant Time Sheet
Sheets("Plant Time").Select
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
'Establish data line
linea = WorksheetFunction.CountA(Range(Cells(1, 2), Cells(10000, 2))) + 2
'Copy information from form to spreadsheet
d = Format(DateDay.Value & "/" & DateMth.Value & "/" & DateYr.Value, "dd/mm/yyyy")
Cells(linea + 1, 2) = DateValue(d) 'Date
Cells(linea + 1, 3) = From1.Value 'Time Start
Cells(linea + 1, 4) = To1.Value 'Time Finish
Cells(linea + 1, 5) = Labour1.Value 'Labour Quantity
Cells(linea + 1, 10) = Product1.Value 'Product Code
Cells(linea + 1, 12) = Code1.Value 'Activity Code
Cells(linea + 1, 14) = Tonnes1.Value 'Tonnes Produced
Cells(linea + 1, 20) = Gas1.Value 'Gas Reading
Cells(linea + 1, 24) = Elect1.Value 'Electricity Reading
Cells(linea + 1, 28) = Comment1.Value 'Comments
For i = 2 To 12
If Me.Controls("To" & i).Value = "" Then GoTo EndLines:
Cells(linea + i, 2) = DateValue(d) 'Date
Cells(linea + i, 3) = Me.Controls("From" & i).Value 'Time Start
Cells(linea + i, 4) = Me.Controls("To" & i).Value 'Time Finish
Cells(linea + i, 5) = Me.Controls("Labour" & i).Value 'Labour Quantity
Cells(linea + i, 10) = Me.Controls("Product" & i).Value 'Product Code
Cells(linea + i, 12) = Me.Controls("Code" & i).Value 'Activity Code
Cells(linea + i, 14) = Me.Controls("Tonnes" & i).Value 'Tonnes Produced
Cells(linea + i, 20) = Me.Controls("Gas" & i).Value 'Gas Reading
Cells(linea + i, 24) = Me.Controls("Elect" & i).Value 'Electricity Reading
Cells(linea + i, 28) = Me.Controls("Comment" & i).Value 'Comments
Next
EndLines:
'Copy formulas down
Lastrow = Range("b" & Rows.Count).End(xlUp).Row
Range("f5:i" & Lastrow).FillDown
Range("k5:k" & Lastrow).FillDown
Range("m5:m" & Lastrow).FillDown
Range("o5:s" & Lastrow).FillDown
Range("u5:w" & Lastrow).FillDown
Range("y5:aa" & Lastrow).FillDown
'Select Yard Bags Sheet
Sheets("Plant Silos").Select
'Establish data line
linea2 = WorksheetFunction.CountA(Range(Cells(1, 2), Cells(10000, 2))) + 2
'Copy information from form to spreadsheet
d = Format(DateDay.Value & "/" & DateMth.Value & "/" & DateYr.Value, "dd/mm/yyyy")
Cells(linea2 + 1, 2) = DateValue(d) 'Date
Cells(linea2 + 1, 3) = SiloProd1.Value
Cells(linea2 + 1, 4) = Silo1.Value
Cells(linea2 + 1, 5) = SiloProd2.Value
Cells(linea2 + 1, 6) = Silo2.Value
Cells(linea2 + 1, 7) = SiloProd3.Value
Cells(linea2 + 1, 8) = Silo3.Value
Cells(linea2 + 1, 9) = SiloProd4.Value
Cells(linea2 + 1, 10) = Silo4.Value
Cells(linea2 + 1, 11) = SiloProd5.Value
Cells(linea2 + 1, 12) = Silo5.Value
Cells(linea2 + 1, 13) = SiloProd6.Value
Cells(linea2 + 1, 14) = Silo6.Value
'Save workbook
ActiveWorkbook.Save
'Select Menu Sheet
Sheets("Menu").Select
DateDay.Value = ""
For i = 1 To 12
Me.Controls("From" & i).Value = ""
Me.Controls("To" & i).Value = ""
Me.Controls("Labour" & i).Value = ""
Me.Controls("Product" & i).Value = ""
Me.Controls("Code" & i).Value = ""
Me.Controls("Gas" & i).Value = ""
Me.Controls("Elect" & i).Value = ""
Me.Controls("Tonnes" & i).Value = ""
Me.Controls("Comment" & i).Value = ""
Next
For h = 1 To 6
Me.Controls("Silo" & h).Value = ""
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
'Clear Userform
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl
End Sub