Submit button to copy all data from sheets to a master sheet

[Roampie]

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.

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.

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

 

[dmt32]

Have you placed this Line: Option Base 1
at the VERY TOP of your Forms code page outside any procedure?

Dave

 

[Roampie]

Have you placed this Line: Option Base 1
at the VERY TOP of your Forms code page outside any procedure?

Dave

Super Dave

It is working now.

Option Base 1 was inside a procedure.

Thank you very much....working 100% now...you may close the thread.

 

[My Aswer Is This]

I do not understand the necessity to clear all the controls. If you use "Unload me" at the end of your script it clears all the controls and closes the UserForm.

 

[Roampie]

I do not understand the necessity to clear all the controls. If you use "Unload me" at the end of your script it clears all the controls and closes the UserForm.

The only reason I don't want to use Unload Me is because I capture alot of invoices to it...so I don't want to close the form after each invoice is submitted.It is just more time consuming, I only use the Unload to go back to the main dashboard(another userform)

 

[Roampie]

"Memories are forever"

I like that signature...lol nice one...

 

[My Aswer Is This]

OK. I understand thanks.

The only reason I don't want to use Unload Me is because I capture alot of invoices to it...so I don't want to close the form after each invoice is submitted.It is just more time consuming, I only use the Unload to go back to the main dashboard(another userform)