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

wrote in message
ps.com...
: 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,
: