Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Report with macro losing links to a particular worksheet after running macro
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 --------------------------------------------------------------------------------------------------------------------------------- Regards, Prasanth Dangeti |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
running a macro on another worksheet | Excel Programming | |||
Editing a worksheet while running a macro | Excel Programming | |||
saved worksheet opens in new with macro error report | Excel Worksheet Functions | |||
Running macro on the perticular worksheet only | Excel Programming | |||
Update links to other workbooks while running macro | Excel Programming |