VBA复制工作表至新工作簿时触发Error 1004无法导航到目标位置报错

这个1004错误是Mac端Excel VBA的高频问题，和前期用宏生成目标工作表的操作直接相关，核心触发点如下：

• 代码使用无参数的Worksheets("ZLastWeekOnly").Copy写法，该写法在Mac版Excel存在已知寻址bug，且代码未显式指定工作表所属的父工作簿，一旦宏运行时活动工作簿发生偏移，直接触发导航失败
• 宏生成的ZLastWeekOnly工作表大概率残留无效配置：包括指向#REF!的失效命名区域、异常打印区域/滚动区域、生成过程中残留的空形状对象，复制时Excel资源校验失败直接抛错
• 目标保存路径位于Dropbox同步目录下，同步进程会占用路径读写权限，加上Mac端Dir函数对带空格、括号的特殊字符路径兼容性差，会进一步放大复制操作的异常

按如下逻辑调整代码即可解决：

• 显式声明所有工作簿、工作表对象，完全弃用依赖ActiveWorkbook/ActiveSheet的隐式调用，避免对象偏移
• 复制前先清理目标工作表的无效残留配置，删除失效命名区域，重置打印、滚动区域
• 先创建空白新工作簿，再将目标工作表拷贝到新工作簿指定位置，替代无参数Copy的高风险写法
• 保存时显式指定xlsx格式编码，关闭不必要的系统弹窗提示

修复后可直接运行的代码：

Sub CopySheetToArchive()
    Dim FName As String
    Dim FPath As String
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim newWB As Workbook
    Dim nm As Name
    
    ' 绑定源工作簿、源工作表对象，宏和目标表在同一文件时用ThisWorkbook即可
    Set sourceWB = ThisWorkbook
    Set sourceWS = sourceWB.Worksheets("ZLastWeekOnly")
    
    FPath = "/Users/phild/Dropbox (SGPS ShowRig)/DDocuments/ARCHIVES/Z_Last Weeks Accounts Archives"
    FName = "New Workbook.xlsx"
    
    Debug.Print "Source WB = " & sourceWB.Name
    Debug.Print "Source WS = " & sourceWS.Name
    
    ' 清理源表无效残留
    sourceWS.PageSetup.PrintArea = ""
    sourceWS.ScrollArea = ""
    For Each nm In sourceWB.Names
        If InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete
    Next nm
    
    ' 新建空白工作簿，指定位置拷贝工作表，规避Mac端无参数Copy的bug
    Set newWB = Workbooks.Add(xlWBATWorksheet)
    sourceWS.Copy Before:=newWB.Sheets(1)
    ' 删除新工作簿默认生成的空白表
    Application.DisplayAlerts = False
    newWB.Sheets(newWB.Sheets.Count).Delete
    Application.DisplayAlerts = True
    
    ' 校验文件存在性并保存
    If Dir(FPath & "/" & FName) <> "" Then
        MsgBox "File " & FPath & "/" & FName & " already exists"
        newWB.Close SaveChanges:=False
    Else
        Application.DisplayAlerts = False
        newWB.SaveAs _
            Filename:=FPath & "/" & FName, _
            FileFormat:=xlOpenXMLWorkbook
        Application.DisplayAlerts = True
        newWB.Close SaveChanges:=True
    End If
End Sub

如果调整代码后仍报错，按以下顺序排查：

• 运行宏前暂时暂停Dropbox对目标存档文件夹的同步，避免文件锁冲突
• 打开ZLastWeekOnly工作表，按F5调出定位窗口，选择定位「对象」，批量删除选中的空残留形状
• 检查ZLastWeekOnly工作表的可见性，若为深度隐藏（xlVeryHidden），先改为可见再执行复制

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