如何停止内部链接转向#REF

Question

i我的工作簿中有一个选项卡（Tab a），该链接在 Application.GetOpenFilename中插入了另一个选项卡（tab b和tab c）。当我运行宏时，我会在该选项卡上的公式中获取 #REF!（公式参考插入的选项卡，即tab b和tab c）。我如何防止这种情况发生并保持完整的公式？

这是我的代码：

Sub Data_Tab()
    '
    ' Data Tab Macro
    Dim ws As Worksheet
    Dim LastCell As Range
    Dim LastCellRowNumber As Long
    Dim sourceSheet As Worksheet
    Set sourceSheet = ActiveSheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("CC_DMSR").Delete
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "CC_DMSR"
    Application.DisplayAlerts = True
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim Sheet As Worksheet
    Dim PasteStart As Range
    Set wb1 = ActiveWorkbook
    Set PasteStart = [CC_DMSR!A1]
    Sheets("CC_DMSR").Select
    Cells.Select
    Selection.ClearContents
    MsgBox "Please select the CC DMSR File"
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a Report to Parse", _
    FileFilter:="Report Files *.xlsx (*.xlsx),")
    If FileToOpen = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    Else
        Set wb2 = Workbooks.Open(Filename:=FileToOpen)
        For Each Sheet In wb2.Sheets
            With Sheet.UsedRange
                .Copy PasteStart
                Set PasteStart = PasteStart.Offset(.Rows.Count)
            End With
        Next Sheet
    End If
    wb2.Close
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Submitted_DMSR").Delete
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "Submitted_DMSR"
    Application.DisplayAlerts = True
    Set wb1 = ActiveWorkbook
    Set PasteStart = [Submitted_DMSR!A1]
    Sheets("Submitted_DMSR").Select
    Cells.Select
    Selection.ClearContents
    MsgBox "Please select the Submitted DMSR File"
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a Report to Parse", _
    FileFilter:="Report Files *.xlsx (*.xlsx),")
    If FileToOpen = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    Else
        Set wb2 = Workbooks.Open(Filename:=FileToOpen)
        For Each Sheet In wb2.Sheets
            With Sheet.UsedRange
                .Copy PasteStart
                Set PasteStart = PasteStart.Offset(.Rows.Count)
            End With
        Next Sheet
    End If
    wb2.Close
    ActiveWorkbook.RefreshAll
    ActiveSheet.PivotTables("PivotTable2").PivotCache.MissingItemsLimit = _
        xlMissingItemsNone
    ActiveSheet.PivotTables("PivotTable1").PivotCache.MissingItemsLimit = _
        xlMissingItemsNone
    With Sheets("Comparison").PivotTables("PivotTable1").PivotFields("TASK/TB")
        .PivotItems("(blank)").Visible = False
    End With
    With Sheets("Comparison").PivotTables("PivotTable2").PivotFields("TASK/TB")
        .PivotItems("(blank)").Visible = False
    End With

    Dim bottomrow As Long
    Sheets("Comparison").Select
    bottomrow = Cells(Rows.Count, "B").End(xlUp).Row
    Application.ScreenUpdating = False
    Range("D4:J4").AutoFill Destination:=Range("D4:J" & bottomrow), Type:=xlFillDefault
    Application.ScreenUpdating = True
    Call sourceSheet.Activate
End Sub

Answer 1

我不确定是否仅打开WB2引入ref错误或插入板的插入。

例如。如果您打开Workbook1，该公式包含对Workbook2的引用，然后以保护模式打开Workbook2（按默认设置），Excel将在任何带有Workbook2引用的单元格中引入Ref错误（由于受保护模式）。<<<<<<<<<<<<<<<<<<<<<<

如果以上是您的问题，则可能是将WB2添加为受信任的文档或位置，或Excel> File>"选项">"选项"并关闭"受保护模式"（Intrvisable）。

否则，您使用的其他解决方案是：

1. range。通过所有单元格中的循环和循环，并参考将插入的表格
   2. 在这些单元格中暂时替换为'='的第一个'='，这应该将公式变成字符串值。
   3. 然后您插入所需的床单（字符串值无法体验参考错误）
   4. 然后用'='替换'='将字符串转换回Excel公式。

您可以在步骤1中设置一个范围对象，然后可以在步骤2和3中重复使用。