VBA将Excel工作表标签重命名为对应文件名报错解决咨询

AI 小北

2026-10-7

错误原因

Workbook.Name属于只读属性，无法直接通过赋值修改，你之前的写法混淆了工作簿对象和工作表对象的属性，你需要修改的是复制到目标工作簿后的工作表名称，而非源工作簿名称。

实现逻辑

工作表执行Copy操作粘贴到目标工作簿后，该新工作表会自动成为目标工作簿的活动工作表，直接修改ActiveSheet.Name为源工作簿的名称即可，也可以提前把后缀名去掉让工作表名称更简洁。

修改后的可运行代码

Sub wwwww()
    Dim wb_source As Workbook
    Dim wb_target As Workbook
    Dim source_file_name As String ' 存储源文件名的变量
    
    Application.DisplayAlerts = False
    
    Set wb_target = Workbooks.Open("c:\Test\MEBIllingOffice.xlsm")
    
    'File #1
    Set wb_source = Workbooks.Open("C:\Test\xAccountARAgingPatient.xlsx")
    ' 提取源文件名，不需要去掉后缀的话直接赋值为wb_source.Name即可
    source_file_name = Replace(wb_source.Name, ".xlsx", "")
    wb_source.Sheets("xAccountARAgingPatient.xlsx").Copy after:=wb_target.Sheets(wb_target.Sheets.Count)
    ' 给刚复制过来的工作表改名
    ActiveSheet.Name = source_file_name
    wb_source.Close
    
    'File #2
    Set wb_source = Workbooks.Open("C:\Test\xAccountARAgingPayer.xlsx")
    source_file_name = Replace(wb_source.Name, ".xlsx", "")
    wb_source.Sheets("xAccountARAgingPayer.xlsx").Copy after:=wb_target.Sheets(wb_target.Sheets.Count)
    ActiveSheet.Name = source_file_name
    wb_source.Close
    
    'File #3
    Set wb_source = Workbooks.Open("C:\Test\xCashAgingAnalysis.xlsx")
    source_file_name = Replace(wb_source.Name, ".xlsx", "")
    wb_source.Sheets("xCashAgingAnalysis.xlsx").Copy after:=wb_target.Sheets(wb_target.Sheets.Count)
    ActiveSheet.Name = source_file_name
    wb_source.Close
    
    'wb_target.Save
    'wb_target.Close
    
    Application.DisplayAlerts = True
End Sub