Hi
I am trying to solve this :
My workbook consist of about 30 sheets.
My master sheet(Sheet1) is called Main
In my userform I fill out a the form for varoius stuff, and copy that form data into the diffrent sheets based on my combobox selection.
Everything seems to be working fine except my Main sheet which still shows empty.
I need all the data from my other worksheets to go into one (Main) sheet.
So in other words if I press the submit button on my userform the data should goto the relevant sheet based on my combo box selection and to my main sheet.
The below code is working fine when filling data in my userform and then submit it to the diffrent sheets based upon my combobox selection.
I have tried this code below, but it doesn't seem to update my Main file.
I am trying to solve this :
My workbook consist of about 30 sheets.
My master sheet(Sheet1) is called Main
In my userform I fill out a the form for varoius stuff, and copy that form data into the diffrent sheets based on my combobox selection.
Everything seems to be working fine except my Main sheet which still shows empty.
I need all the data from my other worksheets to go into one (Main) sheet.
So in other words if I press the submit button on my userform the data should goto the relevant sheet based on my combo box selection and to my main sheet.
The below code is working fine when filling data in my userform and then submit it to the diffrent sheets based upon my combobox selection.
Code:
Private Sub CommandButton5_Click()
Dim strName As String
strName = cmbDep.Value
With Worksheets(cmbDep.Value)
eRow = .Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
.Cells(eRow, "A").Value = txt1.Text
.Cells(eRow, "B").Value = txt2.Text
.Cells(eRow, "C").Value = txtWay.Text
.Cells(eRow, "D").Value = txtInv.Text
.Cells(eRow, "E").Value = txt3.Text
.Cells(eRow, "F").Value = txt4.Text
.Cells(eRow, "G").Value = cmbDep.Text
.Cells(eRow, "H").Value = cmbVec.Text
.Cells(eRow, "I").Value = cmbVecw.Text
.Cells(eRow, "J").Value = txt6.Text
.Cells(eRow, "K").Value = cmbUnit.Text
.Cells(eRow, "L").Value = txt8.Text
.Cells(eRow, "M").Value = txt10.Text
.Cells(eRow, "N").Value = txt11.Text
.Cells(eRow, "O").Value = cmbPayment.Text
.Cells(eRow, "P").Value = txt15.Text
.Cells(eRow, "Q").Value = cmbName.Text
.Cells(eRow, "S").Value = txt13.Text
End With
'Clear input controls.
Me.txt1.Text = ""
Me.txt2.Text = ""
Me.txtWay.Text = ""
Me.txtInv.Text = ""
Me.txt3.Text = ""
Me.txt4.Text = ""
Me.cmbDep.Text = ""
Me.cmbVec.Text = ""
Me.cmbVecw.Text = ""
Me.txt6.Text = ""
Me.cmbUnit.Text = ""
Me.txt8.Text = ""
Me.txt10.Text = ""
Me.txt11.Text = ""
Me.cmbPayment.Text = ""
Me.txt15.Text = ""
Me.cmbName.Text = ""
Me.txt13.Text = ""
End Sub
I have tried this code below, but it doesn't seem to update my Main file.
Code:
Private Sub CommandButton5_Click()
Call PopulateData("Main") 'Replace with the correct sheet name
Call PopulateData(cmbDep.Value)
'Clear input controls.
Me.txt1.Text = ""
Me.txt2.Text = ""
Me.txtWay.Text = ""
Me.txtInv.Text = ""
Me.txt3.Text = ""
Me.txt4.Text = ""
Me.cmbDep.Text = ""
Me.cmbVec.Text = ""
Me.cmbVecw.Text = ""
Me.txt6.Text = ""
Me.cmbUnit.Text = ""
Me.txt8.Text = ""
Me.txt10.Text = ""
Me.txt11.Text = ""
Me.cmbPayment.Text = ""
Me.txt15.Text = ""
Me.cmbName.Text = ""
Me.txt13.Text = ""
End Sub
Sub PopulateData(strWorksheet As String)
With Worksheets(strWorksheet)
eRow = .Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
.Cells(eRow, "A").Value = txt1.Text
.Cells(eRow, "B").Value = txt2.Text
.Cells(eRow, "C").Value = txtWay.Text
.Cells(eRow, "D").Value = txtInv.Text
.Cells(eRow, "E").Value = txt3.Text
.Cells(eRow, "F").Value = txt4.Text
.Cells(eRow, "G").Value = cmbDep.Text
.Cells(eRow, "H").Value = cmbVec.Text
.Cells(eRow, "I").Value = cmbVecw.Text
.Cells(eRow, "J").Value = txt6.Text
.Cells(eRow, "K").Value = cmbUnit.Text
.Cells(eRow, "L").Value = txt8.Text
.Cells(eRow, "M").Value = txt10.Text
.Cells(eRow, "N").Value = txt11.Text
.Cells(eRow, "O").Value = cmbPayment.Text
.Cells(eRow, "P").Value = txt15.Text
.Cells(eRow, "Q").Value = cmbName.Text
.Cells(eRow, "S").Value = txt13.Text
End With
End Sub