input numbers for code

B

Bradly

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.
 
T

Tim Williams

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

Tim


Bradly said:
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
'
'
Click to expand...
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.
Click to expand...
 
B

Bradly

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 said:
See modifications below (marked with *****)

Tim


Bradly said:
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
'
'
Click to expand...
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.
Click to expand...


.
Click to expand...
 
T

Tim Williams

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 said:
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 said:
See modifications below (marked with *****)

Tim


Bradly said:
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
'
'
Click to expand...
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.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

setting more than one filter 1
help with dates in the past 5
filtering two pieces of data 3
stopping a macro 0
cannot insert row in vba 1
Macro to save a workbook 1
Userform to increment line item numbers with textboxes-spinbuttons 5
Macro not saving as 1

Top