Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Why does my link keep breaking after running a macro?

0

Here is my issue,

Every month I import data in a flat list and run a macro to sort it. The information is then linked to another sheet in the workbook for a monthly record for yearly tracking. The problem I am running into is after I run the macro all of my links turn into #REF. Can someone please help me out here.

Additional info: Excel Version 2010, File Extension .XLSM, link using=Circulation!$E$2

' CirculationStep1 Macro
'

'
    Range("A15:B16").Select
    Selection.Cut
    Range("D2").Select
    ActiveSheet.Paste
    Range("A17:B17").Select
    Selection.Cut
    Range("D6").Select
    ActiveSheet.Paste
    Range("A18:B18").Select
    Selection.Cut
    Range("D9").Select
    ActiveSheet.Paste
    Range("A19:B30").Select
    Selection.Cut
    Range("G2").Select
    ActiveSheet.Paste
    Range("A32:B32").Select
    Selection.Cut
    Range("J2").Select
    ActiveSheet.Paste
    Range("A45:B45").Select
    Selection.Cut
    Range("J3").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=54
    Range("A87:B87").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-81
    Range("J4").Select
    ActiveSheet.Paste
    Range("A35:B36").Select
    Selection.Cut
    Range("J11").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=60
    Range("A89:B90").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-63
    Range("J13").Select
    ActiveSheet.Paste
    Range("A31:B31").Select
    Selection.Cut
    Range("J18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A31:B31").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("J18").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Paste
    Range("A31:B31").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("J17").Select
    ActiveSheet.Paste
    Range("A34:B34").Select
    Selection.Cut
    Range("J18").Select
    ActiveSheet.Paste
    Range("A33:B33").Select
    Selection.Cut
    Range("M2").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=3
    Range("A39:B39").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-12
    Range("M3").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=45
    Range("A88:B88").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-66
    Range("M4").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=60
    Range("A91:B91").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-57
    Range("G14").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=51
    Range("A92:B92").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-45
    Range("A15").Select
    ActiveSheet.Paste
    Range("A49:B49").Select
    Selection.Cut
    Range("A16").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-33
    Range("A37:B37").Select
    ActiveWindow.SmallScroll Down:=18
    Range("A56:B71").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-48
    Range("D27").Select
    ActiveSheet.Paste
    Range("A47:B48").Select
    Selection.Cut
    Range("D25").Select
    ActiveSheet.Paste
    Range("A46:B46").Select
    Selection.Cut
    Range("D16").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=45
    Range("A86:B86").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-60
    Range("D17").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=15
    Range("A54:B54").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-15
    Range("G26").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=12
    Range("A54:B54").Select
    Application.CutCopyMode = False
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-12
    Range("G25").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=48
    Range("A85:B85").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-33
    Range("G26").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=3
    Range("A50:B50").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-33
    Range("D12").Select
    ActiveSheet.Paste
    Range("D21:E21").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.SmallScroll Down:=15
    Range("A52:B53").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-27
    Range("D21").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=48
    Range("A84:B84").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-78
    Range("D23").Select
    ActiveSheet.Paste
    Range("D25:E25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A38:B38").Select
    Selection.Cut
    Range("M11").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=12
    Range("A51:B51").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-27
    Range("M12").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=15
    Range("A55:B55").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-36
    Range("M15").Select
    ActiveSheet.Paste
    Range("A40:B44").Select
    Selection.Cut
    Range("M18").Select
    ActiveSheet.Paste
    Range("A37:B37").Select
    Selection.Cut
    Range("M26").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=39
    Range("A72:B83").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=-51
    Range("J27").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=60
    Range("A26:B91").Select
    Range("A91").Activate
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-105
    Range("A17").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"
    Range("A17:B17").Select
    Selection.Font.Bold = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Copy
    Range("D18").Select
    ActiveSheet.Paste
    Range("D24").Select
    ActiveSheet.Paste
    Range("D45").Select
    ActiveSheet.Paste
    Range("G27").Select
    ActiveSheet.Paste
    Range("G15").Select
    ActiveSheet.Paste
    Range("J5").Select
    ActiveSheet.Paste
    Range("J15").Select
    ActiveSheet.Paste
    Range("J19").Select
    ActiveSheet.Paste
    Range("J39").Select
    ActiveSheet.Paste
    Range("M23").Select
    ActiveSheet.Paste
    Range("M12").Select
    Range("M13").Select
    ActiveSheet.Paste
    Range("M5").Select
    ActiveSheet.Paste
    Range("E18").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("E24").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    Range("E45").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    Range("H27").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("H15").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    Range("K5").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    Range("K15").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Range("K19").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("K39").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
    Range("N23").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
    Range("N13").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("N5").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    Range("A1:P49").Select
    Selection.Columns.AutoFit
    Range("K29").Select
    Range("M21:N21").Select
    Selection.Cut
    Range("M29").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Paste
    Range("M21:N21").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("M29").Select
    ActiveSheet.Paste
    Range("M15:N15").Select
    Range("N15").Activate
    Selection.Cut
    Range("M32").Select
    ActiveSheet.Paste
    Range("M21:N21").Select
    Selection.Delete Shift:=xlUp
    Range("M36").Select
End Sub
 

Edit: Additional Information:

I am using a Single Excel File - and using the same macro on multiple sheets.

Sheet Names are Circulation and Inhouse. I am then attempting to move the information onto another form in a format acceptable to my work on a sheet named Circulation Report.  I will attach a sample of the file. - You may notice in the sample under Circulation Report that the links on top are now #ref while the links on the bottom are as I entered them.

Thank you for any help you may offer.

Answer
Discuss

Answers

0

Sorry, I couldn't resist. I simplified your code. The code below is supposed to do everything your code above does - but it's better to check :-)

Sub CirculationStep1()
    ' 28 Oct 2017
    Application.ScreenUpdating = False
    MoveRange "A15:B16", "D2"
    MoveRange "A17:B17", "D6"
    MoveRange "A18:B18", "D9"
    MoveRange "A19:B30", "G2"
    MoveRange "A32:B32", "J2"
    MoveRange "A45:B45", "J3"
    MoveRange "A87:B87", "J4"
    MoveRange "A35:B36", "J11"
    MoveRange "A89:B90", "J13"
    MoveRange "A31:B31", "J17"
    MoveRange "A34:B34", "J18"
    MoveRange "A33:B33", "M2"
    MoveRange "A39:B39", "M3"
    MoveRange "A88:B88", "M4"
    MoveRange "A91:B91", "G14"
    
    MoveRange "A92:B92", "A15"
    MoveRange "A49:B49", "A16"
    MoveRange "A56:B71", "D27"
    MoveRange "A47:B48", "D25"
    MoveRange "A46:B46", "D16"
    MoveRange "A86:B86", "D17"
    MoveRange "A54:B54", "G25"
    MoveRange "A85:B85", "G26"
    MoveRange "A50:B50", "D12"
    
    Range("D21:E21").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    MoveRange "A52:B53", "D21"
    MoveRange "A84:B84", "D23"
    Range("D25:E25").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    MoveRange "A38:B38", "M11"
    MoveRange "A51:B51", "M12"
    MoveRange "A55:B55", "M15"
    MoveRange "A40:B44", "M18"
    MoveRange "A37:B37", "M26"
    MoveRange "A72:B83", "J27"
    
    Range("A26:B91").Delete Shift:=xlUp
    Cells(17, "A").Value = "Total"
    Cells(17, "B").FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"
    With Range("A17:B17")
        .Font.Bold = True
        .Font.Underline = xlUnderlineStyleSingle
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    CopyRange "A17:B17", "D18"
    CopyRange "D24", "D45"
    CopyRange "G27", "G15"
    CopyRange "J5", "J15"
    CopyRange "J19", "M5"
    CopyRange "J19", "M13"
    CopyRange "J19", "M23"
    CopyRange "J19", "J39"
    
    Cells(18, "E").FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Cells(24, "E").FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    Cells(45, "E").FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    Cells(15, "H").FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
    Cells(27, "H").FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Cells(5, "K").FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    Cells(15, "K").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Cells(19, "K").FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Cells(39, "K").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
    Cells(5, "N").FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    Cells(13, "N").FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Cells(23, "N").FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
    
    Range("A1:P49").Columns.AutoFit
    MoveRange "M21:N21", "M29"
    MoveRange "M15:N15", "M32"
    Range("M21:N21").Delete Shift:=xlUp
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub
Private Sub MoveRange(RangeAddress As String, _
                      TargetAddress As String)
    ' 28 Oct 2017
    
    With ActiveSheet
        .Range(RangeAddress).Cut Destination:=.Range(TargetAddress)
    End With
End Sub
Private Sub CopyRange(RangeAddress As String, _
                      TargetAddress As String)
    ' 28 Oct 2017
    
    With ActiveSheet
        .Range(RangeAddress).Copy Destination:=.Range(TargetAddress)
    End With
End Sub

There are two big differences. One is that nothing is ever selected. Actions are carried out behind the scenes and you will never be shown a result until all is done.

The other is in transferring the action from the Main sub to the sub-routines 'MoveRange" and 'CopyRange'. You call these procedures by specifying the range and saying where it's supposed to go, like

CopyRange "A17:B17", "D18"
which will copy A17:B17 to D18. You will find it easier to read and inviting to modify.

Now to your question.
Unfortunately it isn't particularly clear. For one thing, if the 'Circulation' worksheet is in the same workbook as the sheet on which the above code works it isn't a link at all, it's a reference. Links and references both pull data. They can't push them. Therefore, if your formula =Circulation!$E$2 is in the sheet on which the code works it should still pull the data while no changes have been made in 'Circulation'. On the other hand, if the 'Circulation' worksheet is the one on which the code works, and the formula is in another sheet all the cutting and deleting you do provides ample reason for Excel to lose track.

With the simplified code above it will be easy to progressively rem some of the lines out (meaning, prevent the lines from running as code by prefixing them with an apostrophe) until you can pinpoint the one(s) causing the damage. Perhaps you can cure the problem by replacing 'Delete' in your code with 'ClearContents'. Modify 'MoveRange' to copy/paste and clear contents instead of cut. In any event, since you are already using code, it shouldn't be a big issue to re-establish the references if destroying them can't be avoided.

Discuss

Discussion

I have edited my original post with a sample of the file and I hope a little more clarification into what I have done. I did use your code in the macro for both circulation and inhouse macros. - Thank you
libraryaccount Oct 28, '17 at 12:57 pm
In the attached file I did some minor improvements in the code, especially to prevent accidental use on the wrong sheet. Please read the code in the 'Trials' module. You can drag that module to your own project in VBE's Project Explorer window or Export/Import it.
I wasn't able to reproduce the cause of your complaint. On the file you posted all the references were already spoiled. I restored the first four but when I ran my code they stayed intact.
You might replace the 'MoveRange' procedure in the file with this one.
Private Sub MoveRange(RangeAddress As String, _
                      TargetAddress As String)
    ' 29 Oct 2017
    
    CopyRange RangeAddress, TargetAddress
    ActiveSheet.Range(RangeAddress).ClearContents
End Sub

That would eliminate the cutting.
Variatus (rep: 4889) Oct 28, '17 at 11:00 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login