vba - 更改复制的选项卡的名称

Question

我有一些 vba 代码，在下面我循环遍历模板文件的目录，打开任何具有 .xlsx 扩展名的文件，并将每个工作簿中的“报告”选项卡复制到 wbTarget 工作簿。我可以复制工作表，但我想更改已复制到变量 Filename 中的选项卡的名称。

    Public Sub CopySheets()

    Dim wbk As Workbook
    Dim Filename As String
    Dim Path As String
    Path = "C:\combine\"
    Filename = Dir(Path & "*.xlsx")
    Set wbTarget = ActiveWorkbook

    Do While Len(Filename) > 0  
        Set wbk = Workbooks.Open(Path & Filename)
        Sheet = 2
        wbk.Sheets("Reporting").Copy After:=Workbooks("Combined.xlsm").Sheets(Workbooks("Combined.xlsm").Sheets.Count)

        '>>change name of copied tab here<<   

        wbk.Close True
        Filename = Dir
        Sheet = Sheet + 1
    Loop
    End Sub

Answer 1

此行之后

wbk.Sheets("Reporting").Copy After:=Workbooks("Combined.xlsm").Sheets(Workbooks("Combined.xlsm").Sheets.Count) 

尝试

ActiveSheet.Name = "myname" '<== replace with whatever name you want to assign to the tab