代码之家 › 专栏 › 技术社区 › John Carldel Vivo

使用组合框将列从一张工作表剪切并粘贴到另一张工作图

vba excel

John Carldel Vivo · 技术社区 · 4 周前

有人能帮我为我的vba代码创建一个快捷方式吗?在该快捷方式中,它将单元格2剪切并粘贴到表格1中一列的最后一行到单元格2,直到表格2中一列的最终一行。

所以我有两张表,第一张是源表,第二张是结果表。在源代码表上,有一个生成用户表单的按钮。所有组合框都有选项,即“a列”到“Z列”。这些选项引用源工作表中的列。因此,如果用户从组合框1中选择列A,然后单击提交,从列A的第二行开始,直到最后一行被复制并粘贴到结果表中的特定列中。我想知道如何制作下面代码的快捷方式。因此,在提交按钮/命令按钮中,我需要输入所有组合框的所有代码。以下是组合框1的示例。我们将非常感谢您的帮助。

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

2 回复 | 直到 4 周前

Tim Williams 4 周前

这里有一个建议:

Private Sub CommandButton1_Click()
   Dim v, ws As Worksheet
   v = Me.ComboBox1.Value
   If v Like "Column*" Then  'usable value?
       v = Replace(v, "Column ","") 'get just the column letter
       Set ws = Activesheet
       ws.range(ws.cells(2, v), ws.cells(ws.rows.count, v).end(xlup)).copy _
            ThisWorkbook.WorkSheets("Results").Range("E2")  
   End If
End Sub

taller 4 周前

Private Sub CommandButton1_Click()
    Dim i As Long, iOffset As Long, sVal As String
    ' loop through ComboBox 1 ~ 6
    For i = 1 To 6
        sVal = Me.Controls("ComboBox" & i).Value
        If Len(sVal) > 0 Then
            Dim sCol As String
            sCol = Split(sVal)(1)
            iOffset = IIf(i > 4, 2, 0)
            ' Copy data
            Range(sCol & "2", Cells(Rows.Count, sCol).End(xlDown)).Copy Sheets("Results").Cells(2, i + 4 + iOffset)
            Application.CutCopyMode = False
        End If
    Next
    With Sheets("Results")
        ' populate Col C
        .Range("C2:C" & .Cells(.Rows.Count, "E").End(xlUp).Row).Value = Me.TextBox1.Text
    End With
End Sub