Excel：在一个工作表中运行宏会导致来自不同工作表的所有引用都丢失其引用并返回 #REF

Excel: Running a macro in one sheet causes all references from different sheets to this worksheet to lose their reference and return a #REF

提问人：Hajoroeters 提问时间：11/1/2023 最后编辑：JohnMHajoroeters 更新时间：11/5/2023 访问量：64

问：

我目前正在我的报告模型中使用多个宏。我遇到的问题如下：

我使用的第一个宏，复制工作表 A 中的所有数据（数据基于其他工作表中的公式）并将硬值粘贴到工作表 B 中。接下来，我有一个不同的工作表 C，其中的一些数据引用并链接到工作表 B。

当我再次在工作表 1 中运行宏时，工作表 B 中的数据可能会发生变化，这不是问题。但是，工作表 C 中的引用给出了 a 而不是链接到工作表 B 中的正确单元格。#REF!

例如，当我在工作表 C 中使用以下代码时：

=IF(LEFT('WorksheetB'!A1;3)="Yes"; 'WorksheetB'!B1; 0)

当我运行宏时，我得到：

=IF(LEFT('WorksheetB'!#REF!;3)="Yes"; 'WorksheetB'!#REF!; 0)

当工作表 A 中的宏导致工作表 B 中的数据发生变化时，有没有办法保留指向工作表 B 的链接？

我什么都试过了。从使用 F4 锁定单元格到创建另一个宏。

下面是宏的代码：

Sub X()
'
 If Worksheets("Checks").Range("C2").Value > 0.01 Then
        
        MsgBox "One or multiple checks is/are invalid"
        
        Exit Sub
    End If
    Filename = ActiveWorkbook.Name
    Mypath = ThisWorkbook.Path
    Sheets("Output for Exact").Select
    
    Myname = Filename & " " & Range("B2") & " " & "Q" & Sheets("General Input").Range("B5").Text & " EXPORTFILE "
    
    Columns("A:W").Select
    Selection.Copy
    Sheets("Accruals Previous Period").Select
    Range("A1").Select
    Sheets("Output for Exact").Select
    Columns("A:W").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Accruals Previous Period").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    Application.Run "Wisnullenrf3"
    
End Sub
VBA Excel-公式 VBA6

评论

1赞 BigBen 11/1/2023
请提供相关代码，而不仅仅是描述其效果。
1赞 user3598756 11/1/2023
同时尝试显示代码的相关部分
0赞 PeterT 11/1/2023
我怀疑您的宏正在更改或删除 WorksheetB 上的范围。请提供宏的代码。
0赞 Hajoroeters 11/1/2023
我包含了宏的代码，感谢您的快速回复。
1赞 user3598756 11/1/2023
“Wismullenrf3 确保只显示大于 0 的单元格”：Wismullenrf3 中究竟发生了哪些操作？

答：

0赞 Frank Ball 11/2/2023 #1

让我们从清理代码开始：

Sub X()
    If Worksheets("Checks").Range("C2").Value > 0.01 Then
            
            MsgBox "One or multiple checks is/are invalid"
            
            Exit Sub
        End If
        Filename = ActiveWorkbook.Name
        Mypath = ThisWorkbook.Path
        'Sheets("Output for Exact").Select
        
        Myname = Filename & " " & Range("B2") & " " & "Q" & Sheets("General Input").Range("B5").Text & " EXPORTFILE "
        
        'Columns("A:W").Select
        'Selection.Copy
        'Sheets("Accruals Previous Period").Select
        'Range("A1").Select
        Sheets("Output for Exact").Range("A:W").Copy
        Sheets("Accruals Previous Period").Range([the specfic cell where the PasteSpecial is to occur]).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        
        Application.Run "Wisnullenrf3"
        
End Sub

执行 PasteSpecial 的行需要具有需要发生 PasteSpecial 的特定单元格。

你会注意到我摆脱了所有的“选择”。您不必选择一个单元格来对它做一些事情，只需引用该单元格并对它执行需要执行的操作或获取您需要从中提取的内容（Google “面向对象编程”以获取更多详细信息）。

评论

0赞 user3598756 11/2/2023
要继续清理，我建议明确工作表属于哪些工作簿（ActiveWorkbook，ThisWorkbook），并且正确使用缩进也很有用

上一个：根据特定列中的文本将特定行复制到其他工作表

下一个：VBA 代码，用于了解修改 Excel 文件的每个人