Single Macro containing multiple subs

[tony_bender]

I'm trying to write a macro that will check numerous 'If.. Then'
statements and if the condition is met it goes out to a share point and
retrieves a specific Excel file. As soon as it opens that file it
returns to the original macro and checks the next If..then statement.
If the condition is met it retrieves the report, otherwise it goes on
to the next If...Then.

Unfortunately I can't get past the first If...Then statement.

Here's what my macro looks like so far:

Sub Check12WkCodes()
Application.ScreenUpdating = False

Sheets("inputs").Select
'****if condition is met opens Varietal Summary
If Range("H10") <> "" Then GetVS12Report

'****if condition is met opens Class Rank
If Range("I10") <> "" Then GetCR12Report

'****if condition is met opens Brand Rank
If Range("J10") <> "" Then GetBR12Report

'****if condition is met opensSku Rank
If Range("K10") <> "" Then GetSR12Report


End Sub


Here is an example of the corresponding code for the Brand Rank report:



Sub GetBR12Report()
'***These are the brand ranks

Application.ScreenUpdating = False

Sheets("Inputs").Select
Dim BR12 As Range
Set BR12 = Range("J10")

'*******NATIONAL Selections...
If BR12 = 1 Then TUS_FOOD
If BR12 = 2 Then TUS_DRUG
If BR12 = 3 Then TUS_FOOD_DRUG
If BR12 = 4 Then TUS_FD_DRG_LIQ

'*****This continues to cover over 200 different geographic selections

End Sub


Here's an example of the corresponding market selections

Sub TUS_FOOD ()
Workbooks.Open Filename:= _
"\\MyDirectory\reports\Brand Ranks\tus food.xls"
End Sub

I appreciate any ideas.

Thank you,

 

[PaulD]

Well my first guess is your sheets selection is most likely changing, try
something like

with Sheets("inputs")
If .Range("H10") <>...
End With

Paul D

: I'm trying to write a macro that will check numerous 'If.. Then'
: statements and if the condition is met it goes out to a share point and
: retrieves a specific Excel file. As soon as it opens that file it
: returns to the original macro and checks the next If..then statement.
: If the condition is met it retrieves the report, otherwise it goes on
: to the next If...Then.
:
: Unfortunately I can't get past the first If...Then statement.
:
: Here's what my macro looks like so far:
:
: Sub Check12WkCodes()
: Application.ScreenUpdating = False
:
: Sheets("inputs").Select
: '****if condition is met opens Varietal Summary
: If Range("H10") <> "" Then GetVS12Report
:
: '****if condition is met opens Class Rank
: If Range("I10") <> "" Then GetCR12Report
:
: '****if condition is met opens Brand Rank
: If Range("J10") <> "" Then GetBR12Report
:
: '****if condition is met opensSku Rank
: If Range("K10") <> "" Then GetSR12Report
:
:
: End Sub
:
:
: Here is an example of the corresponding code for the Brand Rank report:
:
:
:
: Sub GetBR12Report()
: '***These are the brand ranks
:
: Application.ScreenUpdating = False
:
: Sheets("Inputs").Select
: Dim BR12 As Range
: Set BR12 = Range("J10")
:
: '*******NATIONAL Selections...
: If BR12 = 1 Then TUS_FOOD
: If BR12 = 2 Then TUS_DRUG
: If BR12 = 3 Then TUS_FOOD_DRUG
: If BR12 = 4 Then TUS_FD_DRG_LIQ
:
: '*****This continues to cover over 200 different geographic selections
:
: End Sub
:
:
: Here's an example of the corresponding market selections
:
: Sub TUS_FOOD ()
: Workbooks.Open Filename:= _
: "\\MyDirectory\reports\Brand Ranks\tus food.xls"
: End Sub
:
: I appreciate any ideas.
:
: Thank you,
:

 

[JMB]

Maybe
If Range("H10") <> "" Then Call GetVS12Report