VBA之多工作簿作为单个Sheet（命名为原工作簿名）复制到一个另工作簿

比如文件夹下有test.xlsm(存放宏的文件)、a.xlsx（Sheet1）、b.xlsx（Sheet1）以及x.xlsx（Sheet xxx为第2个Sheet）
我们的任务是将a.xlsx的Sheet1和b.xlsx的Sheet1复制到x.xlsx的Sheet xxx之前

确保启动时光标在test.xlsm中

Dim i As Integer
    For i = 1 To Workbooks.Count
    Cells(i, 1) = Workbooks(i).name
    Next
    Cells.Replace What:="test.xlsm", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="x.xlsx", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False          
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select

此时可以发现test.xlsm中A1、A2单元格中为a.xlsx和b.xlsx的文件名

Dim j As Integer
    Selection.End(xlDown).Select
    j = Selection.Row        'j sheet
    Dim x As Integer
    For x = 1 To j:
        Windows("test.xlsm").Activate
        Dim Sname As String
        Sname = Range("A" & x).Value
        Workbooks(Sname).Activate 
        With Workbooks(Sname)
                .Sheets(1).name = Mid(Sname, 1, Len(Sname) - 5) '更改工作表名称
        End With
        Sheets(1).Copy Before:=Workbooks("x.xlsx").Sheets(2)  
    Next x