存储于Personal.xlsb的Excel宏运行作用于自身而非目标工作簿问题咨询

你遇到的问题是VBA内置对象ThisWorkbook的指向规则导致的：ThisWorkbook永远指代宏代码所在的工作簿，你把宏存在Personal.xlsb里，所以所有复制操作默认都指向了这个隐藏文件，而非你需要的combined.xlsx。
同时你现有代码还存在一处冗余错误：末尾重复把屏幕更新、系统提醒设为False，正常应该是执行完逻辑后改回True，避免后续Excel操作异常。

1. 无需取消隐藏Personal.xlsb的宏调用方法 #

打开combined.xlsx后按Alt+F8调出宏列表，顶部「位置」下拉框选择「所有打开的工作簿」，就能直接找到xxx宏运行，不需要手动取消隐藏Personal.xlsb。

2. 修改xxx宏代码，指定操作目标为combined.xlsx #

修改后的代码如下，核心改动是提前把当前激活的combined.xlsx赋值为目标工作簿，替换所有ThisWorkbook引用，同时修正了屏幕更新、系统提醒的重置逻辑：

Sub xxx()
    Dim SrcPath As String
    SrcPath = "C:\test\"
    Dim SrcFileName As String
    SrcFileName = Dir(SrcPath & "*.xlsx")
    Dim Targetws As Worksheet
    ' 新增：把当前激活的combined.xlsx设为目标工作簿
    Dim TargetWb As Workbook
    Set TargetWb = ActiveWorkbook
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Do While SrcFileName <> ""
        Workbooks.Open SrcPath & SrcFileName
        For Each Targetws In ActiveWorkbook.Sheets
            ' 改这里：把ThisWorkbook替换为我们定义的TargetWb
            Targetws.Copy after:=TargetWb.Worksheets(TargetWb.Worksheets.Count)
        Next Targetws
        Workbooks(SrcFileName).Close
        SrcFileName = Dir()
    Loop
    
    ' 修正：执行完成后恢复屏幕更新和提醒，不要重复设为False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

3. 同逻辑修改azazaz宏（如果需要使用） #

修改后的代码如下：

Sub azazaz()
    Dim SrcPath As String
    SrcPath = "C:\test\testing\"
    Dim SrcFileName As String
    SrcFileName = Dir("C:\test\testing\" & "x*.xlsx")
    Dim Targetws As Worksheet
    Dim wrkBk As Workbook
    ' 新增：指定目标工作簿为当前激活的combined.xlsx
    Dim TargetWb As Workbook
    Set TargetWb = ActiveWorkbook
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Do While SrcFileName <> ""
        Set wrkBk = Workbooks.Open(SrcPath & SrcFileName)
        For Each Targetws In wrkBk.Sheets
            Targetws.Copy after:=TargetWb.Worksheets(TargetWb.Worksheets.Count)
            ' 新增：明确指定操作的是刚复制过来的新工作表，避免引用偏移
            With TargetWb.Worksheets(TargetWb.Worksheets.Count)
                .Cells.ClearFormats
                .Cells.EntireColumn.AutoFit
                .Parent.Windows(1).FreezePanes = False
                .Range("A1").Select
            End With
        Next Targetws
        wrkBk.Close
        SrcFileName = Dir()
    Loop

    For Each Targetws In TargetWb.Sheets
        Dim tbl As ListObject
        Set tbl = Targetws.ListObjects.Add(xlSrcRange, Targetws.Range("A1").CurrentRegion, , xlYes)
        tbl.Name = "tbl" & Targetws.Name
    Next Targetws

    ' 修正：恢复设置
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

每次运行宏前，先点击切换到combined.xlsx的窗口，确保它是当前激活的工作簿，再运行修改后的宏即可，所有操作都会执行在combined.xlsx中。

内容的提问来源于stack exchange，提问作者JL Thalacker