Macro Quandry

[Guest]

I am attempting to run a multi-step If-Then macro. Please see below, it
never gets past the first If statement line.

Sub Forecast()
'
' Forecast Macro
' Macro recorded 07/27/2005 by D. Baron
'

'
If B5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Oct_Forecast"
ElseIf C5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Nov_Forecast"
ElseIf D5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Dec_Forecast"
ElseIf E5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jan_Forecast"
ElseIf F5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Feb_Forecast"
ElseIf G5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Mar_Forecast"
ElseIf H5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Apr_Forecast"
ElseIf I5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!May_Forecast"
ElseIf J5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jun_Forecast"
ElseIf K5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Jul_Forecast"
ElseIf L5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Aug_Forecast"
ElseIf M5 = G1 Then
Application.Run "'SSA Business Review Tool_rev4.xls'!Sep_Forecast"
End If
End Sub

 

[Henry]

Desiree,

Try something like this:

Sub Forecast()
Workbooks.Open ("SSA Business Review Tool_rev4.xls")

With ActiveSheet

Select Case Range("G1").Value

Case Range("B5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Oct_Forecast").Select

Case Range("C5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Nov_Forecast").Select

Case Range("D5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Dec_Forecast").Select

"""""""""""""""""""""""""""""""""""""""""""""""
Etc.,Etc.

Case Range("M5").Value
Workbooks("SSA Business Review
Tool_rev4.xls").Sheets("Sep_Forecast").Select

End Select
End With
End Sub


Henry

 

[Guest]

Just an FYI... This is what ended up working. Thank you so much for your help.

With ActiveSheet

Select Case Range("G1").Value

Case Range("B5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Oct_Forecast"

Case Range("C5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Nov_Forecast"

Case Range("D5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Dec_Forecast"

Case Range("E5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Jan_Forecast"

Case Range("F5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Feb_Forecast"

Case Range("G5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Mar_Forecast"

Case Range("H5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Apr_Forecast"

Case Range("I5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!May_Forecast"

Case Range("J5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Jun_Forecast"

Case Range("K5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Jul_Forecast"

Case Range("L5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Aug_Forecast"

Case Range("M5").Value
Application.Run "'SSA Business Review
Tool_rev4.xls'!Sep_Forecast"
End Select
End With
End Sub