Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the

Sep 1, 2018
Reaction score
Private Sub cboLs_DropButtonClick()

Dim wb As Workbook   'workbook 2 for combobox list
Dim i As Long
Dim ssheet As Worksheet

Set wb = Workbooks.Open("C:\Users\Desktop\Book1.xlsx")
Set ssheet = wb.Worksheets("LS")

If Me.cboLs.ListCount = 0 Then
    For i = 2 To ssheet.Range("A" & ssheet.Rows.Count).End(xlUp).Row
        Me.cboLs.AddItem Sheets("LS").Cells(i, "A").Value
    Next i
End If
End Sub

Private Sub cboLs_Change()

Dim wb As Workbook
Dim ssheet As Worksheet
Dim i As Long

Set wb = Workbooks.Open("C:\Users\Desktop\Book1.xlsx")
Set ssheet = wb.Worksheets("LS")

For i = 2 To ssheet.Range("A" & ssheet.Rows.Count).End(xlUp).Row
    If ssheet.Cells(i, "A").Value = (Me.cboLs) Or ssheet.Cells(i, "A").Value = Val(Me.cboLs) Then
        Me.txtProject = ssheet.Cells(i, "B").Value
    End If
Next i
End Sub

Private Sub cmdadd_Click()
Dim e As Long
Dim Sheet1 As String

Worksheets(Sheet1).Activate  'Workbook-1 here i need to capture my userform data but it is going to workbook-2 on sheetname LS

    'position cursor in the correct cell A6.

    e = 1 'set as the first ID

    'if all the above are false (OK) then carry on.
    'check to see the next available blank row start at cell A6...
    Do Until ActiveCell.Value = Empty
        ActiveCell.Offset(1, 0).Select 'move down 1 row
        e = e + 1 'keep a count of the ID for later use

    'Populate the new data values into the 'Data' worksheet.
    ActiveCell.Value = e 'Next ID number
    ActiveCell.Offset(0, 2).Value = Me.txtname.Text 'set col B
    ActiveCell.Offset(0, 3).Value = Me.txtbook.Text 'set col C
    ActiveCell.Offset(0, 1).Value = Me.cboLs.Text 'set col D

    Me.txtname.Text = Empty
    Me.txtbook.Text = Empty
    Me.cboLs.Text = Empty

End Sub

I have workbook-1 where the data should be actually captured from the userform when add button is clicked.

In workbook-2 I just have my combobox list inorder to display the excel data when selected from the comobox and textbox automatically in the userform.

But now I am facing a problem, when I fill the userform by selecting all combobox list and filling other data manually then clicked on add button the data is trasferring to my Workbook-2 (below my combobox list).

How to capture the userform data to my workbook-1 on Sheetname "Sheet1".

My Workbook-2 path is "C:\Users\Desktop\Work.xlmx", will I need to include this path also for the commandbutton?


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question