Excel宏实现多表合并并区分数据来源

Sub archer()
Dim MyPath, MyName, AWbName
    Dim Wb As Workbook, WbN As String
    Dim G As Long
    Dim Num As Long
    Dim BOX As String
    Dim sht As Worksheet
    Application.ScreenUpdating = False
    MyPath = ActiveWorkbook.Path
    MyName = Dir(MyPath & "\" & "*.xls")
    AWbName = ActiveWorkbook.Name
    Num = 0
    Do While MyName <> ""
    If MyName <> AWbName Then
    Set Wb = Workbooks.Open(MyPath & "\" & MyName)
    For Each sht In Wb.Sheets
    lr = sht.Cells(Rows.Count, "e").End(3).Row
    With sht
    .Range("a:a").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("a1").Value = "工作表名"
    .Range("a2:a1" & lr) = ActiveWorkbook.Name
    .Range("a1:a" & lr).Borders.LineStyle = xlContinuous
    End With
    Cells.EntireColumn.AutoFit
Next sht
    Num = Num + 1
    With Workbooks(1).ActiveSheet
    .Cells(.Range("B65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)
    For G = 1 To Sheets.Count
    Wb.Sheets(G).UsedRange.Copy .Cells(.Range("B65536").End(xlUp).Row + 1, 1)