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]

Hi welcome to board.

Not tested but see if this alternative to your code helps:

Option Base 1
Private Sub CommandButton5_Click()
    Dim ws As Worksheet
    Dim strName As String
    Dim arr As Variant
    Dim i As Integer
    Dim eRow As Long
    
    strName = Me.cmbDep.Text
    
    ReDim arr(1 To UBound(ControlsArray))
    
    For i = LBound(ControlsArray) To UBound(ControlsArray)
        arr(i) = Me.Controls(ControlsArray(i)).Text
        Me.Controls(ControlsArray(i)).Text = ""
    Next


    For Each ws In ThisWorkbook.Sheets(Array(strName, "Main"))
    
        eRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    
        ws.Cells(eRow, 1).Resize(1, UBound(arr)).Value = arr
    Next


     
End Sub


Function ControlsArray() As Variant
ControlsArray = Array("txt1", "txt2", "txtWay", "txtInv", "txt3", "txt4", _
                    "cmbDep", "cmbVec", "cmbVecw", "txt6", "cmbUnit", "txt8", _
                    "txt10", "txt11", "cmbPayment", "txt15", "cmbName", "txt13")
End Function

Dave

 

[Roampie]

Hi Dave

Thank you for the reply.

Getting a error in the code to this --->>>

arr(i) = Me.Controls(ControlsArray(i)).Text

.Text shouldn't it be .Value instead...tried it with that still getting a error.

Runtime error 9

Subscript out of range is the error it is giving me.

 

[Roampie]

Hi Dave

Thank you for the reply.

Getting a error in the code to this --->>>

arr(i) = Me.Controls(ControlsArray(i)).Text

.Text shouldn't it be .Value instead...tried it with that still getting a error.

Runtime error 9

Subscript out of range is the error it is giving me.

I have to do it this way aswell --->>> this was only my testing file with 5 sheets Sheet1=Main,Sheet2=Tools ect.

Still if command button is pressed it is only updating the other sheets based on the combobox and not the main one aswell.

Private Sub CommandButton1_Click()

    Dim strName As String
    
    strName = ComboBox1.Value
    
    With Worksheets(strName)
        Dim lrow As Range
        Set lrow = Worksheets(strName).Range("A65536").End(xlUp).Offset(1, 0)
        lrow.Value = TextBox1.Value
    End With
    
    Unload Me


End Sub

 

[dmt32]

Did you copy all the code including the Function & Option Base 1 at the top?
Solution as published should do what you want.

Dave

 

[Roampie]

Yip everything.

 

[Roampie]

Yip everything.

 arr(i) = Me.Controls(ControlsArray(i)).Text

If I run the userform and put in some data and submit it, it goes directly to VB editor and debugging showing to the above code (out of range)

 

[dmt32]

Check the Function ControlsArray and ensure array matches all the textbox controls names.

Dave

 

[Roampie]

Will do so, Thanks Dave

 

[Roampie]

Okay the Function ControlsArray

ControlsArray = Array("txt1", "txt2", "txtWay", "txtInv", "txt3", "txt4", _                    "cmbDep", "cmbVec", "cmbVecw", "txt6", "cmbUnit", "txt8", _
                    "txt10", "txt11", "cmbPayment", "txt15", "cmbName", "txt13")

is matching all of my textboxes, combo boxes.

I see at "txt13" there is no , behind it -->> should it be like that?

Sorry I am no coder, just trying to learn from my mistakes.