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.