如何用VBA宏将多工作表移至新Excel并移除所有标签中的"Test"

以下是满足需求的完整VBA代码，已修复原代码语法问题，并新增移除工作表标签“Test”、处理公式引用的功能：

Sub OpenAndMove()
    Dim folderPath As String
    Dim wbWP As Workbook
    Dim wbTest As Workbook
    Dim wbOutput As Workbook
    Dim ws As Worksheet
    Dim newSheetName As String
    
    ' 获取当前工作簿路径
    folderPath = ThisWorkbook.Path
    ' 关闭弹窗和屏幕刷新提升执行效率
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    ' 打开WP.xlsx并另存为Test.xlsx
    Set wbWP = Workbooks.Open(folderPath & "\WP.xlsx")
    wbWP.SaveAs Filename:=folderPath & "\Test.xlsx", FileFormat:=xlOpenXMLWorkbook
    Set wbTest = ActiveWorkbook
    
    ' 创建output.xlsx工作簿
    Set wbOutput = Workbooks.Add
    wbOutput.SaveAs Filename:=folderPath & "\output.xlsx", FileFormat:=xlOpenXMLWorkbook
    
    ' 遍历Test.xlsx的所有工作表，复制到output.xlsx并完成后续处理
    For Each ws In wbTest.Worksheets
        ' 复制工作表到output.xlsx末尾
        ws.Copy After:=wbOutput.Sheets(wbOutput.Sheets.Count)
        
        ' 移除工作表标签中的"Test"字样
        newSheetName = Replace(wbOutput.Sheets(wbOutput.Sheets.Count).Name, "Test", "")
        ' 容错处理：避免替换后出现空工作表名
        If newSheetName = "" Then newSheetName = ws.Name
        wbOutput.Sheets(wbOutput.Sheets.Count).Name = newSheetName
        
        ' 批量处理当前工作表中引用Test.xlsx的公式
        With wbOutput.Sheets(wbOutput.Sheets.Count).UsedRange
            ' 将公式中的[Test.xlsx]外部引用标识替换为空，转为内部引用
            .Replace What:="[Test.xlsx]", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
        End With
    Next ws
    
    ' 保存并关闭所有涉及的工作簿
    wbOutput.Save
    wbOutput.Close
    wbTest.Close SaveChanges:=False
    wbWP.Close SaveChanges:=False
    
    ' 恢复Excel默认设置
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

关键说明 #

• 语法修正：修复原代码中变量声明错误、对象引用拼写错误、路径拼接遗漏反斜杠等问题
• 工作表标签处理：通过Replace函数移除“Test”字样，同时添加空名容错逻辑，避免命名异常
• 公式引用处理：遍历工作表已使用区域，批量替换公式中的外部引用标识，转为工作簿内部引用
• 资源优化：明确对象引用，减少对ActiveWorkbook的依赖，操作结束后恢复Excel弹窗和屏幕刷新默认状态

内容的提问来源于stack exchange，提问作者anjali joshi