常用功能加载宏——多个工作表合并到一个工作表

      <button id="rbbtnMergeSht" label="合并工作表" onAction="rbbtnMergeSht" imageMso="TableInsert" />

Sub rbbtnMergeSht(control As IRibbonControl)
    Call MShtWk.MergeSht
End Sub

Sub MergeSht()
    Dim rngout As Range
    
    On Error Resume Next
    Set rngout = Application.InputBox("请选择输出单元格，输出单元格所在Sheet将不会被复制，但数据会覆盖。", Default:=ActiveCell.Address, Type:=8)
    On Error GoTo 0
    
    If rngout Is Nothing Then
        Exit Sub
    End If
    
    Dim flagHead As Boolean '记录是否复制了标题
    Dim rows As Long
    Dim cols As Long
    Dim sht As Worksheet
    For Each sht In Worksheets
        If sht.Name <> rngout.Parent.Name Then         
            With sht
                '取消筛选
                .AutoFilterMode = False
                '按第一列定位，获取表格的最后所在的行   
                rows = .Cells(Cells.rows.Count, 1).End(xlUp).Row
                If rows > 1 Then
                    '获取表格的列的范围
                    cols = .Cells(1, Cells.Columns.Count).End(xlToLeft).Column
                    
                    '复制标题
                    If Not flagHead Then
                        .Range("A1").Resize(1, cols).Copy rngout
                        rngout.Offset(0, cols).Resize(1, 1).Value = "SheetName"
                        Set rngout = rngout.Offset(1, 0)
                        flagHead = True
                    End If
                    
                    '复制数据
                    .Range("A2").Resize(rows - 1, cols).Copy rngout
                    
                    '如果只需要复制数值：
'                    .Range("A2").Resize(rows - 1, cols).Copy
'                    rngout.PasteSpecial xlPasteValues

                    '如果需要，可以增加一列Sheet名称
                    rngout.Offset(0, cols).Resize(rows - 1, 1).Value = .Name
                    
                    '输出单元格进行偏移
                    Set rngout = rngout.Offset(rows - 1, 0)
                End If
            End With
        End If
    Next
    
End Sub