excel - 导入数据并重命名工作表

Question

我有多个带有要导入的按钮的模板表。这个想法是，每次“挑选”另一个工作簿时，表格“摘要”的相应数据都会通过按钮导入到活动表格中。但是，我不想用其他工作簿中的文件名的一部分重命名活动表，例如工作簿以导入 Test_somthing .. 然后在导入数据“测试 - 摘要”之后。

我知道我快到了，但特别是重命名工作表是一个问题。

Sub Import()
  Dim FileNames As Variant
    Dim FileName As Variant
    Dim WSNew1 As Worksheet
    Dim ActiveListWB As Workbook
    Set DestinationWB = ThisWorkbook.ActiveSheet


    FileNames = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", _
                                               Title:="Select Foreto Import", _
                                               MultiSelect:=True)


    If VarType(FileNames) = vbBoolean Then
        If Not FileNames Then Exit Sub
    End If


    For Each FileName In FileNames

        Set WSNew1 = ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
        'I know this is adding the sheet not only copying the sheet data

        Set ActiveListWB = Workbooks.Open(FileName)
        ActiveListWB.Sheets("Summary").Range("D2:CI206").Copy
        DestinationWB.Range("A1:Z40").PasteSpecial xlPasteFormats

        WSNew1.Name = Split(ActiveListWB.Name, "_")(0) & " - Summary"
        'wan't to rename active in active workbook not import and rename a sheet        

        ActiveListWB.Close False
    Next FileName

End Sub

标签： excelvba