Search code examples
excelvba

Cut and Paste columns from one sheet to another using combo box

can anyone help me to create a shortcut for my vba code wherein it cuts and paste cell 2 until the last row of one column from sheet 1 to the cell 2 until the last row of one column in sheet 2.

So I have 2 Sheets, the first one is for the Source and the second one is the Results. On the source sheet, there is a button that generates a userform. All combo boxes have options namely "Column A" until "Column Z". These options references to the columns in Source Sheet. So what will happen is, if the user selects Column A from the Combo box1, then click submit, starting from the 2nd row of column A until the last row will be copied and paste into a particular column in Results Sheet. I would like to know how canI make a shortcut of the below code. So in the submit button/command button, I need to input all the codes for all combo boxes. Here is an example for combo box 1. Your help will be highly appreciated. enter image description here enter image description here

Private Sub CommandButton1_Click()

If Me.ComboBox1.Value = "Column A" Then
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Results").Select
    Range("E2").Select
    ActiveSheet.Paste
    
End If

If Me.ComboBox1.Value = "Column B" Then
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Results").Select
    Range("E2").Select
    ActiveSheet.Paste
    
End If

If Me.ComboBox1.Value = "Column C" Then
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Results").Select
    Range("E2").Select
    ActiveSheet.Paste
    
End If

If Me.ComboBox1.Value = "Column D" Then
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Results").Select
    Range("E2").Select
    ActiveSheet.Paste
    
End If

End Sub
Solution

  • EDIT: more generic - push the copy process into a standalone sub.

    Here's one suggestion:

    Private Sub CommandButton1_Click()
      
   CopyColumn Me.ComboBox1.Value, "E"
   CopyColumn Me.ComboBox2.Value, "F"
   CopyColumn Me.ComboBox3.Value, "G"
   CopyColumn Me.ComboBox4.Value, "H"
   CopyColumn Me.ComboBox5.Value, "K"
   
End Sub

'copy data from column identified by `cbVal` to row 2 in column `destColLetter`
Sub CopyColumn(ByVal cbVal, destColLetter)
    Dim colSrc As String, ws As Worksheet
    If cbVal Like "Column*" Then  'usable value?
       colSrc = Replace(cbVal, "Column ", "") 'get just the column letter
       Set ws = ActiveSheet
       ws.Range(ws.Cells(2, colSrc), ws.Cells(ws.Rows.Count, colSrc).End(xlUp)).Copy _
            ThisWorkbook.Worksheets("Results").Cells(2, destColLetter)
   End If
End Sub