如何在Excel表头下方新增行并插入UserForm数据（MSO 2021）

需求说明 #

• 在“database2”工作表的表格表头下方首行新增一行，保留原有行的公式和格式
• 将UserForm中的控件数据复制到新增行对应列
• 清空UserForm所有控件内容
• 刷新ListBox数据
• 保存当前工作簿
• 弹出提交成功提示

现有代码问题 #

当前代码存在以下问题导致功能异常：

1. 未明确指定工作表，Range操作可能在当前激活工作表执行，而非目标“database2”工作表
2. 新增行后未复制原有行的格式和公式，导致新行无格式/公式继承
3. 直接通过固定单元格地址赋值，若表格结构变化会失效，且未利用ListObject的结构化特性

修正后的VBA代码 #

Private Sub btnsubmit_Click()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim newRow As ListRow
    Dim sourceRow As Range
    
    ' 指定目标工作表和表格
    Set ws = ThisWorkbook.Worksheets("database2")
    Set tbl = ws.ListObjects(1) ' 若表格有名称，可替换为 tbl.Name = "你的表格名称"
    
    ' 1. 在表格顶部新增行，并复制上一行的格式和公式
    Set newRow = tbl.ListRows.Add(1) ' 在表头下首行插入新行
    Set sourceRow = tbl.ListRows(2).Range ' 取原首行（新增后的第2行）作为格式/公式来源
    sourceRow.Copy
    newRow.Range.PasteSpecial Paste:=xlPasteFormats ' 复制格式
    newRow.Range.PasteSpecial Paste:=xlPasteFormulas ' 复制公式
    Application.CutCopyMode = False ' 清除复制状态
    
    ' 2. 将UserForm数据写入新增行
    With newRow.Range
        .Columns(1).Value = Now ' A列：当前时间
        .Columns(2).Value = Me.cmbor.Value ' B列
        .Columns(3).Value = Me.cmbun.Value ' C列
        .Columns(4).Value = Me.cmbna.Value ' D列
        .Columns(5).Value = Me.cmbad.Value ' E列
        .Columns(6).Value = Me.cmbci.Value ' F列
        .Columns(7).Value = Me.cmbprod.Value ' G列
        .Columns(8).Value = Me.cmbm.Value ' H列
        .Columns(9).Value = Me.cmbh.Value ' I列
        .Columns(10).Value = Me.cmb2.Value ' J列
        .Columns(11).Value = Me.cmbj.Value ' K列
        .Columns(12).Value = Me.cmbx.Value ' L列
        .Columns(13).Value = Me.cmbsc.Value ' M列
        .Columns(24).Value = Me.cmbtr.Value ' X列
    End With
    
    ' 3. 清空UserForm控件
    Me.cmbor.Value = ""
    Me.cmbun.Value = ""
    Me.cmbna.Value = ""
    Me.cmbad.Value = ""
    Me.cmbci.Value = ""
    Me.cmbprod.Value = ""
    Me.cmbm.Value = ""
    Me.cmbh.Value = ""
    Me.cmb2.Value = ""
    Me.cmbj.Value = ""
    Me.cmbx.Value = ""
    Me.cmbsc.Value = ""
    Me.cmbtr.Value = ""
    
    ' 4. 刷新ListBox数据
    Refresh_Data
    
    ' 5. 保存工作簿
    ThisWorkbook.Save
    
    ' 6. 弹出提示消息
    MsgBox "数据已成功提交并保存！", vbInformation, "提交完成"
End Sub

关键修复说明 #

• 明确操作目标：通过Worksheets("database2")和ListObject锁定操作对象，避免激活工作表干扰
• 继承格式与公式：新增行后复制原首行的格式和公式，保证新行与原有行格式一致、公式正常生效
• 结构化赋值：用newRow.Range.Columns(n)代替固定单元格地址，适配表格结构变更
• 清除复制状态：用Application.CutCopyMode = False避免剪贴板残留影响后续操作

内容的提问来源于stack exchange，提问作者Magjack