How do I keep macro running after running this piece of code?

[z3115]

Hi All,

I have the following macro, which I did not write myself. It works perfectly, but now I want to add in some additional steps after this code finishes running. The problem is that Excel seems to just ignore everything I put after this piece. Any idea why?

    Dim wsName As String
    Dim RangeError As Boolean
    '--------------------------------
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    On Error GoTo NoEssbaseRange
    '- main loop
    For Each ws In Worksheets
        wsName = ws.Name
        RangeError = False
        Application.GoTo Reference:=ws.range("_essbase")
        If Not RangeError Then Application.Run Macro:="EssMenuRetrieve"
    Next
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Exit Sub
    '------------------------------------------------------------------
    '- handle the Excel error produced when the range does not exist
    '- comment out the MsgBox line unless you need to see each one
NoEssbaseRange:
    'MsgBox ("Worksheet '" & wsName & "' has no essbase range.")
    RangeError = True   ' stops retrieve running in the next line above
    Resume Next         ' keep the macro running

 

[ChrisBM]

Exit Sub

After that statement, the macro quits. Any code you want to add has to go before that statement is called. Just put it directly above that line.

 

[z3115]

OK that makes sense, doing that appears to mess up the original piece of code though, and gives me an error. Any thoughts? Thanks!

Exit Sub

After that statement, the macro quits. Any code you want to add has to go before that statement is called. Just put it directly above that line.

 

[par60056]

you will need to show us what you added.

 

[z3115]

you will need to show us what you added.

Here's the piece I pasted first along with everything after it (as you can see I have 2 different variations of the original piece):

'Run the first Hyperion Retrieval
    Dim wsName As String
    Dim RangeError As Boolean
    '--------------------------------
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    On Error GoTo NoEssbaseRange
    '- main loop
    For Each ws In Worksheets
        wsName = ws.Name
        RangeError = False
        Application.GoTo Reference:=ws.range("_essbase")
        If Not RangeError Then Application.Run Macro:="EssMenuRetrieve"
    Next
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    'Exit Sub
    '------------------------------------------------------------------
    '- handle the Excel error produced when the range does not exist
    '- comment out the MsgBox line unless you need to see each one
NoEssbaseRange:
    'MsgBox ("Worksheet '" & wsName & "' has no essbase range.")
    RangeError = True   ' stops retrieve running in the next line above
    'Resume Next         ' keep the macro running


'Part 2
Sheets("Show Details 2").Cells.ClearContents
Sheets("NIE-ALL").Cells.Copy
Sheets("Show Details 2").range("A1").PasteSpecial
Application.CutCopyMode = False


Sheets("Show Details").Select
    range("A3").Select
range(selection, selection.End(xlDown)).Copy
Sheets("Show Details 2").range("B2").PasteSpecial Transpose:=True


'Run the 2nd Hyperion retrieval
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    On Error GoTo NoEssbaseRange
    '- main loop
    For Each ws In Worksheets
        wsName = ws.Name
        RangeError = False
        Application.GoTo Reference:=ws.range("_essbase2")
        If Not RangeError Then Application.Run Macro:="EssMenuRetrieve"
    Next
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    'Exit Sub
    '------------------------------------------------------------------
    '- handle the Excel error produced when the range does not exist
    '- comment out the MsgBox line unless you need to see each one
'NoEssbaseRange:
    'MsgBox ("Worksheet '" & wsName & "' has no essbase range.")
    RangeError = True   ' stops retrieve running in the next line above
    Resume Next         ' keep the macro running
    
Sheets("FHolder").Visible = False 'Hide the support tabs
Sheets("TO-ALL").Visible = False
Sheets("NIE-ALL").Visible = False


Sheets("Show Details 2").Select


Exit Sub






End Sub

If anyone can help I would be extremely grateful, thank you!

 

[par60056]

you cannot just comment out the exit sub. it then falls into the error handler.

You can try this:

'Run the first Hyperion Retrieval    Dim wsName As String
    Dim RangeError As Boolean
    '--------------------------------
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    On Error GoTo NoEssbaseRange
    '- main loop
    For Each ws In Worksheets
        wsName = ws.Name
        RangeError = False
        Application.GoTo Reference:=ws.range("_essbase")
        If Not RangeError Then Application.Run Macro:="EssMenuRetrieve"
    Next
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
'Part 2
Sheets("Show Details 2").Cells.ClearContents
Sheets("NIE-ALL").Cells.Copy
Sheets("Show Details 2").range("A1").PasteSpecial
Application.CutCopyMode = False




Sheets("Show Details").Select
    range("A3").Select
range(selection, selection.End(xlDown)).Copy
Sheets("Show Details 2").range("B2").PasteSpecial Transpose:=True




'Run the 2nd Hyperion retrieval
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    On Error GoTo NoEssbaseRange
    '- main loop
    For Each ws In Worksheets
        wsName = ws.Name
        RangeError = False
        Application.GoTo Reference:=ws.range("_essbase2")
        If Not RangeError Then Application.Run Macro:="EssMenuRetrieve"
    Next
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
Sheets("FHolder").Visible = False 'Hide the support tabs
Sheets("TO-ALL").Visible = False
Sheets("NIE-ALL").Visible = False




Sheets("Show Details 2").Select
   
    Exit Sub
    '------------------------------------------------------------------
    '- handle the Excel error produced when the range does not exist
    '- comment out the MsgBox line unless you need to see each one
NoEssbaseRange:
    'MsgBox ("Worksheet '" & wsName & "' has no essbase range.")
    RangeError = True   ' stops retrieve running in the next line above
    'Resume Next         ' keep the macro running




End Sub