For..Next Problem

[Alan P]

i'm having difficulty getting the code below to do what i want. the intent
is to enter the month once and have the program retain that value as it
cycles through the numbered worksheets and performs the appropriate
subroutine. i've tried a couple of approaches but either it doesn't retain
the value on subsequent worksheets or i can't get past the For Each..Next -
it doesn't recognize the For Each portion when it gets to the end.

Any thoughts would be appreciated. Thanks,

Alan

____
Sub Prepare_All_Worksheets2()

If MsgBox("Are you sure you want to move current forecast data to the prior
section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo,
"Move Forecast Data") = vbYes Then

Dim prepMonth As String

prepMonth = InputBox("What is the month you are preparing for?")



Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
If IsNumeric(ActiveSheet.Name) Then

If prepMonth = "November" Then
Prepare_Nov
Else
If prepMonth = "December" Then
Prepare_Dec
Else
If prepMonth = "January" Then
Prepare_Jan
Else
If prepMonth = "February" Then
Prepare_Feb
Else
If prepMonth = "March" Then
Prepare_Mar
Else
If prepMonth = "April" Then
Prepare_Apr
Else
If prepMonth = "May" Then
Prepare_May
Else
If prepMonth = "June" Then
Prepare_Jun
Else
If prepMonth = "July" Then
Prepare_Jul
Else
If prepMonth = "August" Then
Prepare_Aug
Else
If prepMonth = "September" Then
Prepare_Sep
Else
MsgBox "There was a problem loading the data."
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Next ws

Else
'USER CANCELLED
MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move
Forecast Data"
End If

End Sub

 

[Mike H]

Alan

Declare Prepmonth as Public and take the declaration out of your code and
you can pass the value to your Subs. While your code may work I'd suggest you
look at select case instead of all those nested ifs.

Public prepmonth As String
Sub Prepare_All_Worksheets2()

'Dim prepmonth As String ' delete this lline

Mike

 

[Joel]

Not usure if these changes will solve allyou problems because you didn't post
you other routines. The code below will work. If you are still having
problems look at the rest of your code.


Sub Prepare_All_Worksheets2()

If MsgBox("Are you sure you want to move current forecast data to the prior
section?" & vbCrLf & "(All Numbered Worksheets!)", vbQuestion + vbYesNo,
"Move Forecast Data") = vbYes Then

Dim prepMonth As String

prepMonth = InputBox("What is the month you are preparing for?")



Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
If IsNumeric(ws.Name) Then

select case preMonth

Case "November"
Prepare_Nov

Case "December"
Prepare_Dec

Case "January"
Prepare_Jan

Case "February"
Prepare_Feb

Case "March"
Prepare_Mar

Case"April"
Prepare_Apr

Case "May"
Prepare_May

Case "June"
Prepare_Jun

Case "July"
Prepare_Jul

case "August"
Prepare_Aug

Case "September"
Prepare_Sep

Case Else
msgbox("Incoreect Month - Exiting")
exit sub
else
MsgBox "There was a problem loading the data."
End If

Next ws

Else
'USER CANCELLED
MsgBox "Forecast data has NOT been moved.", vbCritical + vbOKOnly, "Move
Forecast Data"
End If

 

[Stefi]

What about this approach?

prepMonth = InputBox("What is the month you are preparing for?")
monthnum = Str(Month(DateValue("2008." & x & ".1")))
Worksheets(monthnum).Select

prepare (monthnum)

This way you can directly select the desired worksheet, don't need a cycle.
I think you don't need 12 subs for months, you can use the same one with a
month parameter.

Regards,
Stefi

„Alan P†ezt írta:

 

[Gary Keramidas]

not sure how to verify whant they inout into the inout box but, if they type in
month name, all you should need is to type the first 3 letters:

Sub test()
Dim prepmonth As String
Dim MacroToRun As String
prepmonth = InputBox("What is the month you are preparing for?")
MacroToRun = "prepare_" & prepmonth
Application.Run "testbook2!" & MacroToRun ' change name of workbook
End Sub

 

[Gary Keramidas]

these were supposed to be input, keyboard must have moved on me.<g>

not sure how to verify what they input into the input box but, if they type in
month name, all you should need is to type the first 3 letters:

 

[Alan P]

Thanks to all for your help. this is a rush job and i wasn't sure if Case
would work, thus the If statements. no awards for code excellence around
here.
thanks again.

Alan