Report with macro losing links to a particular worksheet after running macro

[santhu]

Hello All,

I have written the following macro (although I am still new to writing
macros) to run a report. However, after running the macro, the links
between two particular worksheets are missing i.e. cells are getting
copy paste special - values.

However, before running the macro it is perfectly okay. Can anyone
please help me?
-------------------------------------------------------------------------------------------------------------------------------
Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal username As Variant, ByVal password As Variant, ByVal
server As Variant, ByVal application As Variant, ByVal database As
Variant) As Long
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal range As Variant, ByVal lockFlag As Variant) As Long
Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal
sheetName As Variant, ByVal item As Variant, ByVal sheetOption As
Variant) As Long
Declare Function EssVZoomIn Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal range As Variant, ByVal selection As Variant, ByVal
level As Variant, ByVal across As Variant) As Long

Sub RunMacro()
Dim cubename As String
cubename = "Rank - Scenario 2"
X = EssVConnect(cubename, "", "", "Agfrpdap1.Ea.Com", cubename,
cubename)
If X <> 0 Then
MsgBox ("Connection failure.")
End If
Sheets("Rank - Scenario 1").Select
X = EssVSetSheetOption(Null, 1, 1)
X = EssVSetSheetOption(Null, 2, True)
X = EssVSetSheetOption(Null, 6, True)
X = EssVSetSheetOption(Null, 7, True)
X = EssVZoomIn(Null, Null, range("a6"), 1, False)
Sheets("Rank - Scenario 1").Select
range("A6").Select
range(selection, selection.End(xlDown)).Select
range(selection, selection.End(xlToRight)).Select
selection.Sort Key1:=range("D6"), Order1:=xlDescending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortTextAsNumbers
X = EssVSetSheetOption(Null, 2, False)
X = EssVZoomIn(Null, Null, range("a7:a1000"), 1, False)
range("A6").Select
range(selection, selection.End(xlDown)).Select
range(selection, selection.End(xlToRight)).Select
selection.Sort Key1:=range("D6"), Order1:=xlDescending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortTextAsNumbers
selection.Copy
Sheets("Rank - Scenario 2").Select
range("a6").Select
ActiveSheet.Paste
range("A6").Select
range(selection, selection.End(xlDown)).Select
range(selection, selection.End(xlToRight)).Select
selection.Sort Key1:=range("h6"), Order1:=xlDescending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortTextAsNumbers
Sheets("Report Control").Select
range("a2").Select
MsgBox ("The Macro has successfully run.")
End Sub