编译错误原因 #

你遇到的「Compile error: Invalid use of New keyword」错误，是因为不能直接用New UserForm动态创建未预先定义的窗体。VBA中UserForm是需要先在VBA编辑器里插入的窗体模块类，直接用New关键字调用会触发语法错误。

修正后的完整代码 #

下面是修复错误并实现「仅合并选中工作表」功能的代码，同时优化了部分逻辑：

Sub MergeSelectedWorksheets()
    Dim wrk As Workbook
    Dim sht As Worksheet
    Dim trg As Worksheet
    Dim rng As Range
    Dim colCount As Integer
    Dim selectedSheets As Collection
    Dim frm As Object ' 改用Object类型动态创建窗体
    Dim chk As MSForms.CheckBox
    Dim btnOK As MSForms.CommandButton
    Dim i As Integer
    Dim topPos As Integer
    Dim isOKClicked As Boolean
    
    ' 初始化选中工作表集合
    Set selectedSheets = New Collection
    
    ' 动态创建用户窗体
    Set frm = CreateObject("UserForm")
    With frm
        .Caption = "选择要合并的工作表"
        .Width = 220
        .Height = 320
        .StartUpPosition = 1 ' 屏幕中心显示
    End With
    
    ' 添加工作表复选框
    topPos = 10
    For Each sht In ActiveWorkbook.Worksheets
        Set chk = frm.Controls.Add("Forms.CheckBox.1")
        With chk
            .Caption = sht.Name
            .Top = topPos
            .Left = 15
            .Width = 180
            .Height = 20
            ' 可选：隐藏工作表默认不勾选
            .Value = Not sht.Visible = xlSheetHidden
            topPos = topPos + 22
        End With
    Next sht
    
    ' 添加确认按钮并绑定事件
    Set btnOK = frm.Controls.Add("Forms.CommandButton.1")
    With btnOK
        .Caption = "确定"
        .Top = topPos + 15
        .Left = 60
        .Width = 80
        .Height = 26
        .Default = True
        ' 绑定按钮点击事件
        .OnClick = "btnOK_Click"
    End With
    
    ' 声明按钮事件过程（动态生成临时模块）
    Dim btnCode As String
    btnCode = "Sub btnOK_Click()" & vbCrLf & _
              "    isOKClicked = True" & vbCrLf & _
              "    Unload Me" & vbCrLf & _
              "End Sub"
    ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule).CodeModule.AddFromString btnCode
    
    ' 显示窗体
    isOKClicked = False
    frm.Show
    
    ' 收集选中的工作表
    If isOKClicked Then
        For Each chk In frm.Controls
            If TypeName(chk) = "CheckBox" And chk.Value = True Then
                Set sht = ActiveWorkbook.Worksheets(chk.Caption)
                selectedSheets.Add sht
            End If
        Next chk
    End If
    
    ' 清理临时模块和窗体
    On Error Resume Next
    ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents("Module1")
    On Error GoTo 0
    Unload frm
    Set frm = Nothing
    
    ' 判断是否选中工作表
    If selectedSheets.Count = 0 Then
        MsgBox "未选择任何要合并的工作表。", vbExclamation, "无选择"
        Exit Sub
    End If
    
    Set wrk = ActiveWorkbook
    
    ' 检查合并工作表是否已存在
    For Each sht In wrk.Worksheets
        If sht.Name = "合并结果" Then
            MsgBox "已存在名为「合并结果」的工作表，请先删除或重命名后再操作。", vbOKOnly + vbExclamation, "错误"
            Exit Sub
        End If
    Next sht
     
    Application.ScreenUpdating = False
     
    ' 创建合并结果工作表
    Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
    trg.Name = "合并结果"
    
    ' 获取表头列数
    colCount = selectedSheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
    
    ' 复制表头
    With trg.Cells(1, 1).Resize(1, colCount)
        .Value = selectedSheets(1).Cells(1, 1).Resize(1, colCount).Value
        .Font.Bold = True
    End With
     
    ' 合并选中工作表的数据
    For i = 1 To selectedSheets.Count
        Set sht = selectedSheets(i)
        ' 获取数据区域（从第2行到最后一行）
        Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(Rows.Count, 1).End(xlUp).Resize(, colCount))
        ' 复制到合并表
        trg.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
    Next i
    
    trg.Columns.AutoFit
    Application.ScreenUpdating = True
    
    MsgBox "已成功合并 " & selectedSheets.Count & " 个工作表到「合并结果」。", vbInformation, "合并完成"
End Sub

关键修正点 #

• 替换窗体创建方式：用CreateObject("UserForm")替代New UserForm，实现动态创建临时窗体，无需预先插入UserForm模块。
• 添加按钮事件绑定：通过动态生成代码模块，给确认按钮绑定点击事件，确保窗体能正确关闭并标记用户操作。
• 优化细节：
  • 隐藏工作表默认不勾选（可根据需求调整）
  • 用Columns.Count替代固定值255，适配不同Excel版本的列数
  • 清理临时生成的代码模块，避免冗余

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