I have a training register workbook with three worksheets. Each worksheet contains a table
Sheet Name - Table Name
TrainingReg - tblTrainingReg
TrainingActivities - tblTrainingAct
EmployeeInfo - tblEmployeeInfo
What I Want: If a new activity is added to tblTrainingAct then the new activity is also added to tblTraingReg for each employee.
For Example: There are 12 employees in tblEmployeeInfo. Then If a new activity e.g. Cleaning is added to tblTrainingAct then the activity will be added 12 times to tblTrainingReg as well.
I have underlined the parts of the code I am struggling with. I may also have completely missed the boat with the approach I am taking.
I know the rest of my code is clunky but I am new to vba and will work on cleaning up the code once I have achieved my goal.
Private Sub cmdAddActivity_Click() 'Commands executed when "Click to Transfer Information" is chosen
Dim Alastrow As Long
'looks for last filled row in tblEmployeeInfo
Alastrow = ThisWorkbook.Worksheets("TrainingActivities").Cells(Rows.Count, 1).End(xlUp).Row
'Transfers information typed in user form to tblEmployeeInfo
With ThisWorkbook.Worksheets("TrainingActivities")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1) = txtActivityID.Value
txtActivityID.Text = Application.Max(.Range("A:A")) + 1
.Cells(Alastrow + 1, 2).Value = txtTrainingAct.Text
.Cells(Alastrow + 1, 3).Value = ComboBoxType.Text
End With
'Empties form and closes window
Unload Me
'Goes to TrainingReg sheet in Workbook
Worksheets("TrainingActivities").Activate
'This is the lines of code I am struggling with
'If a new activity is added to tblTrainingAct then new activity is added to tblTrainingReg for each employee in tblEmployeeInfo
Dim NewActivity As Variant
Dim Elastrow As Long
Dim i As Long
Dim tblActivity As Object, LastRow As Object
Set tblActivity = Worksheets("TrainingActivities").ListObjects("tblTrainingAct")
Set LastRow = Worksheets("TrainingReg").ListObjects("tblTrainingReg").ListRows.Add
Elastrow = ThisWorkbook.Worksheets("EmployeeInfo").Cells(Rows.Count, 1).End(xlUp).Row
NewActivity = txtTrainingAct.Value
If NewActivity = "" Then
MsgBox ("No New Activity Added")
Else
For i = 2 To Elastrow '(from the first row in tblEmployeeInfo to the last row)
tblActivity.Range.Cells 'line of code not complete
LastRow.Range.Offset(0, 3).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next i
End If
End Sub
Private Sub UserForm_Initialize() 'when the user form starts up
With UserForm2.txtActivityID
txtActivityID.Text = Application.Max(Range("A:A")) + 1 'automatically generate next available Training Activity ID number (IDs are sequential)
End With
With UserForm2.ComboBoxType 'fill combo box with available options
.AddItem "Performance Review"
.AddItem "QIS / QIC"
.AddItem "SOP / Verbal"
.AddItem "Control Chart - CRM / QC / SPIKE"
.AddItem "Duplicate Sample / Spike Recovery"
End With
End Sub
Sheet Name - Table Name
TrainingReg - tblTrainingReg
TrainingActivities - tblTrainingAct
EmployeeInfo - tblEmployeeInfo
What I Want: If a new activity is added to tblTrainingAct then the new activity is also added to tblTraingReg for each employee.
For Example: There are 12 employees in tblEmployeeInfo. Then If a new activity e.g. Cleaning is added to tblTrainingAct then the activity will be added 12 times to tblTrainingReg as well.
I have underlined the parts of the code I am struggling with. I may also have completely missed the boat with the approach I am taking.
I know the rest of my code is clunky but I am new to vba and will work on cleaning up the code once I have achieved my goal.
Private Sub cmdAddActivity_Click() 'Commands executed when "Click to Transfer Information" is chosen
Dim Alastrow As Long
'looks for last filled row in tblEmployeeInfo
Alastrow = ThisWorkbook.Worksheets("TrainingActivities").Cells(Rows.Count, 1).End(xlUp).Row
'Transfers information typed in user form to tblEmployeeInfo
With ThisWorkbook.Worksheets("TrainingActivities")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1) = txtActivityID.Value
txtActivityID.Text = Application.Max(.Range("A:A")) + 1
.Cells(Alastrow + 1, 2).Value = txtTrainingAct.Text
.Cells(Alastrow + 1, 3).Value = ComboBoxType.Text
End With
'Empties form and closes window
Unload Me
'Goes to TrainingReg sheet in Workbook
Worksheets("TrainingActivities").Activate
'This is the lines of code I am struggling with
'If a new activity is added to tblTrainingAct then new activity is added to tblTrainingReg for each employee in tblEmployeeInfo
Dim NewActivity As Variant
Dim Elastrow As Long
Dim i As Long
Dim tblActivity As Object, LastRow As Object
Set tblActivity = Worksheets("TrainingActivities").ListObjects("tblTrainingAct")
Set LastRow = Worksheets("TrainingReg").ListObjects("tblTrainingReg").ListRows.Add
Elastrow = ThisWorkbook.Worksheets("EmployeeInfo").Cells(Rows.Count, 1).End(xlUp).Row
NewActivity = txtTrainingAct.Value
If NewActivity = "" Then
MsgBox ("No New Activity Added")
Else
For i = 2 To Elastrow '(from the first row in tblEmployeeInfo to the last row)
tblActivity.Range.Cells 'line of code not complete
LastRow.Range.Offset(0, 3).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next i
End If
End Sub
Private Sub UserForm_Initialize() 'when the user form starts up
With UserForm2.txtActivityID
txtActivityID.Text = Application.Max(Range("A:A")) + 1 'automatically generate next available Training Activity ID number (IDs are sequential)
End With
With UserForm2.ComboBoxType 'fill combo box with available options
.AddItem "Performance Review"
.AddItem "QIS / QIC"
.AddItem "SOP / Verbal"
.AddItem "Control Chart - CRM / QC / SPIKE"
.AddItem "Duplicate Sample / Spike Recovery"
End With
End Sub
