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,