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

Question

I am currently working with multiple macros in my reporting model. The problem I encounter is the following:

The first macro that I use, copies all data in worksheet A (data is based on formula's from other sheets) and pastes the hard values into worksheet B. Next, I have a different worksheet C where some data refers and links to worksheet B.

When I run the macro in worksheet 1 again, the data might change in worksheet B, which is not a problem. However, the references in worksheet C give a #REF! instead of linking to the correct cell in worksheet B.

For example, when I use the following code in worksheet C:

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

When I then run the macro, I get:

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

Is there a way to keep the link to worksheet B, when a macro in worksheet A causes the data in worksheet B to change?

I tried everything. From locking the cells with F4 to creating another macro.

Here is the code of the Macro:

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

Answer 1

Let's start with cleaning up your code:

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

The line where you're doing the PasteSpecial needs to have the specific cell where the PasteSpecial needs to happen.

You'll notice that I got rid of all the "Selects". You don't have to select a cell to do something with it, just refer to the cell and do what needs to be done to it or get what you need to extract from it (Google "Object Oriented Programming" for more details).