|
Macro Quandry
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 |
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 "Desiree" wrote in message ... 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 |
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 "Henry" wrote: 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 "Desiree" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com