I have a spreadsheet of work orders with start dates in column G and finish
dates in column H. I would like to be able to pull all records between
certain start and finish dates - this would change each month (ie all records
between April 1 (start) and April 30 (finish) and place on a blank sheet.
I want to be prompted to enter the dates. How would I do this?

I think you'll find this to be of some help. To get the code into your
workbook, open it and press [Alt]+[F11] to open the VB Editor. In the VBE,
choose Insert | Module and copy and paste the code below into the module.
Make changes to the code the source sheet name) as required.

Be careful when you paste it into the module. The editor here often breaks
code lines early. That leads to errors in the code. You may have to edit
the copied code to make broken statements one long line of code again. You
can quickly test for this by clicking [Debug] in the VBE menu and choosing
[Compile...] It will flag statements that have gotten broken up. Fix them
one at a time, using [Debug] | [Compile...] after each fix until no more
errors are highlighted.

Close the VB Editor.

To use it, select the destination sheet and then choose Tools | Macro |
Macros from the Excel menu and choose the name of the macro and click the
[Run] button.

Sub MoveByDates()
'you must run this macro
'with the destination sheet selected
Const srcSheetName = "Sheet1"
Const startDateCol = "G"
Dim rowToCopy As Range
Dim listRange As Range
Dim anyListEntry As Range
Dim myStartDate As Date
Dim myEndDate As Date

On Error Resume Next ' in case no date entered
myStartDate = InputBox("Enter Starting Date:", "Start Date Entry", "")
If Err < 0 Then
Err.Clear
MsgBox "No start date entered"
Exit Sub
End If
myEndDate = InputBox("Enter Ending Date:", "End Date Entry", "")
If Err < 0 Then
Err.Clear
MsgBox "No end date entered"
Exit Sub
End If
On Error GoTo 0 ' clear error trapping
If myEndDate < myStartDate Then
MsgBox "End date cannot be before the start date"
Exit Sub
End If
'looks ok, start processing
'assume rows 2 through n contain data in col G
Set listRange = Worksheets(srcSheetName). _
Range(startDateCol & "2:" & startDateCol & _
Worksheets(srcSheetName).Range( _
startDateCol & Rows.Count).End(xlUp).Row)
For Each anyListEntry In listRange
If anyListEntry = myStartDate And _
anyListEntry.Offset(0, 1) <= myEndDate Then
'this falls into the date range
anyListEntry.EntireRow.Copy
'put the entry on active sheet in
'next available row (empty cell in col G)
Range("A" & Range(startDateCol & Rows.Count). _
End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
End If
Next
Application.Goto Range("A1"), True
End Sub


"Carla" wrote:

I have a spreadsheet of work orders with start dates in column G and finish
dates in column H. I would like to be able to pull all records between
certain start and finish dates - this would change each month (ie all records
between April 1 (start) and April 30 (finish) and place on a blank sheet.
I want to be prompted to enter the dates. How would I do this?

Use Pivot Table.
No code or formulas needed:
http://www.savefile.com/files/1551249

"Herbert Seidenberg" wrote:

Use Pivot Table.
No code or formulas needed:
http://www.savefile.com/files/1551249

I am not that experienced with pivot tables - how do get the grouping box to
come up that allows you to pick by month?

Thanks, but I'm still having problems. It seems to be hung up on this part:

Set listRange = Worksheets(2008PMTracking). _
Range(startDateCol & "2:" & startDateCol & _
Worksheets(2008PMTracking).Range( _startDateCol & Rows.Count).End(xlUp).Row)

2008PMTracking is the name of the sheet that contains all my data. Do I
need to change anything else. the error message when I run the macro is
"compile error: syntax error"

"JLatham" wrote:

I think you'll find this to be of some help. To get the code into your
workbook, open it and press [Alt]+[F11] to open the VB Editor. In the VBE,
choose Insert | Module and copy and paste the code below into the module.
Make changes to the code the source sheet name) as required.

Be careful when you paste it into the module. The editor here often breaks
code lines early. That leads to errors in the code. You may have to edit
the copied code to make broken statements one long line of code again. You
can quickly test for this by clicking [Debug] in the VBE menu and choosing
[Compile...] It will flag statements that have gotten broken up. Fix them
one at a time, using [Debug] | [Compile...] after each fix until no more
errors are highlighted.

Close the VB Editor.

To use it, select the destination sheet and then choose Tools | Macro |
Macros from the Excel menu and choose the name of the macro and click the
[Run] button.

Sub MoveByDates()
'you must run this macro
'with the destination sheet selected
Const srcSheetName = "Sheet1"
Const startDateCol = "G"
Dim rowToCopy As Range
Dim listRange As Range
Dim anyListEntry As Range
Dim myStartDate As Date
Dim myEndDate As Date

On Error Resume Next ' in case no date entered
myStartDate = InputBox("Enter Starting Date:", "Start Date Entry", "")
If Err < 0 Then
Err.Clear
MsgBox "No start date entered"
Exit Sub
End If
myEndDate = InputBox("Enter Ending Date:", "End Date Entry", "")
If Err < 0 Then
Err.Clear
MsgBox "No end date entered"
Exit Sub
End If
On Error GoTo 0 ' clear error trapping
If myEndDate < myStartDate Then
MsgBox "End date cannot be before the start date"
Exit Sub
End If
'looks ok, start processing
'assume rows 2 through n contain data in col G
Set listRange = Worksheets(srcSheetName). _
Range(startDateCol & "2:" & startDateCol & _
Worksheets(srcSheetName).Range( _
startDateCol & Rows.Count).End(xlUp).Row)
For Each anyListEntry In listRange
If anyListEntry = myStartDate And _
anyListEntry.Offset(0, 1) <= myEndDate Then
'this falls into the date range
anyListEntry.EntireRow.Copy
'put the entry on active sheet in
'next available row (empty cell in col G)
Range("A" & Range(startDateCol & Rows.Count). _
End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
End If
Next
Application.Goto Range("A1"), True
End Sub


"Carla" wrote:

I have a spreadsheet of work orders with start dates in column G and finish
dates in column H. I would like to be able to pull all records between
certain start and finish dates - this would change each month (ie all records
between April 1 (start) and April 30 (finish) and place on a blank sheet.
I want to be prompted to enter the dates. How would I do this?

RightClick the "Start" cell of the Pivot Table
Group and Show Details Group Month
Do the same for "Finish".

Thank you! I think I finally figured it out and it works like a charm.

Much appreciated!!!!!

"Carla" wrote:


Thanks, but I'm still having problems. It seems to be hung up on this part:

Set listRange = Worksheets(2008PMTracking). _
Range(startDateCol & "2:" & startDateCol & _
Worksheets(2008PMTracking).Range( _startDateCol & Rows.Count).End(xlUp).Row)

2008PMTracking is the name of the sheet that contains all my data. Do I
need to change anything else. the error message when I run the macro is
"compile error: syntax error"

"JLatham" wrote:

I think you'll find this to be of some help. To get the code into your
workbook, open it and press [Alt]+[F11] to open the VB Editor. In the VBE,
choose Insert | Module and copy and paste the code below into the module.
Make changes to the code the source sheet name) as required.

Be careful when you paste it into the module. The editor here often breaks
code lines early. That leads to errors in the code. You may have to edit
the copied code to make broken statements one long line of code again. You
can quickly test for this by clicking [Debug] in the VBE menu and choosing
[Compile...] It will flag statements that have gotten broken up. Fix them
one at a time, using [Debug] | [Compile...] after each fix until no more
errors are highlighted.

Close the VB Editor.

To use it, select the destination sheet and then choose Tools | Macro |
Macros from the Excel menu and choose the name of the macro and click the
[Run] button.

Sub MoveByDates()
'you must run this macro
'with the destination sheet selected
Const srcSheetName = "Sheet1"
Const startDateCol = "G"
Dim rowToCopy As Range
Dim listRange As Range
Dim anyListEntry As Range
Dim myStartDate As Date
Dim myEndDate As Date

On Error Resume Next ' in case no date entered
myStartDate = InputBox("Enter Starting Date:", "Start Date Entry", "")
If Err < 0 Then
Err.Clear
MsgBox "No start date entered"
Exit Sub
End If
myEndDate = InputBox("Enter Ending Date:", "End Date Entry", "")
If Err < 0 Then
Err.Clear
MsgBox "No end date entered"
Exit Sub
End If
On Error GoTo 0 ' clear error trapping
If myEndDate < myStartDate Then
MsgBox "End date cannot be before the start date"
Exit Sub
End If
'looks ok, start processing
'assume rows 2 through n contain data in col G
Set listRange = Worksheets(srcSheetName). _
Range(startDateCol & "2:" & startDateCol & _
Worksheets(srcSheetName).Range( _
startDateCol & Rows.Count).End(xlUp).Row)
For Each anyListEntry In listRange
If anyListEntry = myStartDate And _
anyListEntry.Offset(0, 1) <= myEndDate Then
'this falls into the date range
anyListEntry.EntireRow.Copy
'put the entry on active sheet in
'next available row (empty cell in col G)
Range("A" & Range(startDateCol & Rows.Count). _
End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
End If
Next
Application.Goto Range("A1"), True
End Sub


"Carla" wrote:

I have a spreadsheet of work orders with start dates in column G and finish
dates in column H. I would like to be able to pull all records between
certain start and finish dates - this would change each month (ie all records
between April 1 (start) and April 30 (finish) and place on a blank sheet.
I want to be prompted to enter the dates. How would I do this?