how to select the row of combobox value then copy and paste to another worksheet range using command button with VBA Excel

Question

my userform contains a combobox and a command button.

the combo box list is populated by range("clmA") by identifying my range clmA in the row source field in the my combobox's properties. (NO VBA was used to populate the list)

I want a sub button_click() to select the row of data identified by the value in the combobox and paste the row into another worksheet.

The copy and pasting code should not be a problem I hope however I can't seem to reference or select the value in the combo box.

I am just learning VBA, I am learning by reading this website and using the macro recorder.

Checkout my example here, it uses a listbox but it is the same principle.youtube.com/watch?v=YRdpw7mn9-g — Davesexcel
it worked like a charm but the data I have is actually going from table to table; so instead of copying the entire row, how can I copy the row of the table? — BezukhovPierre

Davesexcel Davesexcel · Accepted Answer · 2015-05-14T20:18:31

Change the columns to whatever you need,

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim Drng As Range, c As Range
    Set ws = Sheets("Data")

    Set Drng = ws.Columns("A:A").SpecialCells(xlCellTypeConstants, 23)
    For Each c In Drng.Cells
        If c = ListBox1 Then
            Range(Cells(c.Row, "A"), Cells(c.Row, "D")).Copy Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next c
    Unload Me
End Sub

how to select the row of combobox value then copy and paste to another worksheet range using command button with VBA Excel

1 Answers