Pulling data by start/finish dates

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?

HelpFrom
Sun May 11 09:12:00 CDT 2008

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?

Herbert
Sun May 11 09:49:06 CDT 2008

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

Carla
Mon May 12 19:45:00 CDT 2008



"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?

Carla
Mon May 12 19:59:00 CDT 2008


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?

Herbert
Mon May 12 21:48:28 CDT 2008

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

Carla
Sat May 17 08:12:02 CDT 2008

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?