I have a macro that currently pulls cases from a master list and sorts them
by the month that case reviews are due. I maintain this file for 6 months.
Currently, the months I am pulling are March through August. However, next
month, I will be pulling April through September, then May through October,
etc. Since I am a novice with code, the only way I know to change the months
sorted is to go into the code each month and adjust the month date in each of
6 modules. The dates in the list and code are in the format of "03 10", "04
10", etc.

Here is the code:

Sub SortMonth1()
'
' SortMonth1 Macro
'
'

'
Sheets("Reviews Due").Activate
Application.Goto Reference:="R1C1"


Dim FilterRange As Range
Dim CopyRange As Range
Set FilterRange = Range("M1:M10000") 'Header in row
Set CopyRange = Range("A1:M10000")
FilterRange.AutoFilter Field:=1, Criteria1:="03 10"
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Month 1").Range("A3")
Application.CutCopyMode = False
Sheets("Reviews Due").Activate
Selection.AutoFilter
Application.Goto Reference:="R1C1"
Sheets("Month 1").Activate
Application.Goto Reference:="R1C1"


ActiveCell.Offset(rowOffset:=2, columnOffset:=0).Activate
Selection.EntireRow.Delete
Application.Goto Reference:="R1C1"
ActiveCell.Offset(rowOffset:=0, columnOffset:=10).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Total Due"
ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C:R[9999]C)"
Selection.Offset(0, -2).Range("A1:C1").Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Application.Goto Reference:="R1C1"
End Sub

Is there any way to add a message box or some efficient way to enter the
next month's date without having to go into the code and change each module
each month (this module is for "03 10"--when next month comes around, I am
looking for an efficient way to change this module to "04 10", etc.)?

Thanks.

See modifications below (marked with *****)

Tim


"Bradly" wrote in message
...
I have a macro that currently pulls cases from a master list and sorts them
by the month that case reviews are due. I maintain this file for 6
months.
Currently, the months I am pulling are March through August. However,
next
month, I will be pulling April through September, then May through
October,
etc. Since I am a novice with code, the only way I know to change the
months
sorted is to go into the code each month and adjust the month date in each
of
6 modules. The dates in the list and code are in the format of "03 10",
"04
10", etc.

Here is the code:

Sub SortMonth1()
'
' SortMonth1 Macro
'
'
Dim mon_yr '***********
mon_yr = Application.Inputbox(Prompt:="Enter month and Year (mm
yy)", _
Type:=2) '********
if not mon_yr then exit sub '*******


'
Sheets("Reviews Due").Activate
Application.Goto Reference:="R1C1"


Dim FilterRange As Range
Dim CopyRange As Range
Set FilterRange = Range("M1:M10000") 'Header in row
Set CopyRange = Range("A1:M10000")
FilterRange.AutoFilter Field:=1, Criteria1:= mon_yr '*********
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Month 1").Range("A3")
Application.CutCopyMode = False
Sheets("Reviews Due").Activate
Selection.AutoFilter
Application.Goto Reference:="R1C1"
Sheets("Month 1").Activate
Application.Goto Reference:="R1C1"


ActiveCell.Offset(rowOffset:=2, columnOffset:=0).Activate
Selection.EntireRow.Delete
Application.Goto Reference:="R1C1"
ActiveCell.Offset(rowOffset:=0, columnOffset:=10).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Total Due"
ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C:R[9999]C)"
Selection.Offset(0, -2).Range("A1:C1").Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Application.Goto Reference:="R1C1"
End Sub

Is there any way to add a message box or some efficient way to enter the
next month's date without having to go into the code and change each
module
each month (this module is for "03 10"--when next month comes around, I am
looking for an efficient way to change this module to "04 10", etc.)?

Thanks.

I tried what you suggested, but I get the following: " Run-time error '13':
Type mismatch " and it is highlighting the "If Not mon_yr Then" portion of
the code. Have I typed this in wrong?

Dim mon_yr
mon_yr = Application.InputBox(prompt:="Enter month and year (mm yy)", Type:=2)
If Not mon_yr Then Exit Sub


"Tim Williams" wrote:

See modifications below (marked with *****)

Tim


"Bradly" wrote in message
...
I have a macro that currently pulls cases from a master list and sorts them
by the month that case reviews are due. I maintain this file for 6
months.
Currently, the months I am pulling are March through August. However,
next
month, I will be pulling April through September, then May through
October,
etc. Since I am a novice with code, the only way I know to change the
months
sorted is to go into the code each month and adjust the month date in each
of
6 modules. The dates in the list and code are in the format of "03 10",
"04
10", etc.

Here is the code:

Sub SortMonth1()
'
' SortMonth1 Macro
'
'
Dim mon_yr '***********
mon_yr = Application.Inputbox(Prompt:="Enter month and Year (mm
yy)", _
Type:=2) '********
if not mon_yr then exit sub '*******


'
Sheets("Reviews Due").Activate
Application.Goto Reference:="R1C1"


Dim FilterRange As Range
Dim CopyRange As Range
Set FilterRange = Range("M1:M10000") 'Header in row
Set CopyRange = Range("A1:M10000")
FilterRange.AutoFilter Field:=1, Criteria1:= mon_yr '*********
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Month 1").Range("A3")
Application.CutCopyMode = False
Sheets("Reviews Due").Activate
Selection.AutoFilter
Application.Goto Reference:="R1C1"
Sheets("Month 1").Activate
Application.Goto Reference:="R1C1"


ActiveCell.Offset(rowOffset:=2, columnOffset:=0).Activate
Selection.EntireRow.Delete
Application.Goto Reference:="R1C1"
ActiveCell.Offset(rowOffset:=0, columnOffset:=10).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Total Due"
ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C:R[9999]C)"
Selection.Offset(0, -2).Range("A1:C1").Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Application.Goto Reference:="R1C1"
End Sub

Is there any way to add a message box or some efficient way to enter the
next month's date without having to go into the code and change each
module
each month (this module is for "03 10"--when next month comes around, I am
looking for an efficient way to change this module to "04 10", etc.)?

Thanks.



.

Sorry - I have a language-switching problem.

Dim mon_yr
mon_yr = Application.InputBox( _
Prompt:="Enter month and Year (mm yy)", _
Type:=2)

If mon_yr = False Or Len(mon_yr) = 0 Then Exit Sub

'rest of code

Tim


"Bradly" wrote in message
...
I tried what you suggested, but I get the following: " Run-time error '13':
Type mismatch " and it is highlighting the "If Not mon_yr Then" portion of
the code. Have I typed this in wrong?

Dim mon_yr
mon_yr = Application.InputBox(prompt:="Enter month and year (mm yy)",
Type:=2)
If Not mon_yr Then Exit Sub


"Tim Williams" wrote:

See modifications below (marked with *****)

Tim


"Bradly" wrote in message
...
I have a macro that currently pulls cases from a master list and sorts
them
by the month that case reviews are due. I maintain this file for 6
months.
Currently, the months I am pulling are March through August. However,
next
month, I will be pulling April through September, then May through
October,
etc. Since I am a novice with code, the only way I know to change the
months
sorted is to go into the code each month and adjust the month date in
each
of
6 modules. The dates in the list and code are in the format of "03
10",
"04
10", etc.

Here is the code:

Sub SortMonth1()
'
' SortMonth1 Macro
'
'
Dim mon_yr '***********
mon_yr = Application.Inputbox(Prompt:="Enter month and Year (mm
yy)", _
Type:=2)
'********
if not mon_yr then exit sub '*******


'
Sheets("Reviews Due").Activate
Application.Goto Reference:="R1C1"


Dim FilterRange As Range
Dim CopyRange As Range
Set FilterRange = Range("M1:M10000") 'Header in row
Set CopyRange = Range("A1:M10000")
FilterRange.AutoFilter Field:=1, Criteria1:= mon_yr '*********
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Month 1").Range("A3")
Application.CutCopyMode = False
Sheets("Reviews Due").Activate
Selection.AutoFilter
Application.Goto Reference:="R1C1"
Sheets("Month 1").Activate
Application.Goto Reference:="R1C1"


ActiveCell.Offset(rowOffset:=2, columnOffset:=0).Activate
Selection.EntireRow.Delete
Application.Goto Reference:="R1C1"
ActiveCell.Offset(rowOffset:=0, columnOffset:=10).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Total Due"
ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C:R[9999]C)"
Selection.Offset(0, -2).Range("A1:C1").Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Application.Goto Reference:="R1C1"
End Sub

Is there any way to add a message box or some efficient way to enter
the
next month's date without having to go into the code and change each
module
each month (this module is for "03 10"--when next month comes around, I
am
looking for an efficient way to change this module to "04 10", etc.)?

Thanks.



.