Extract Rows By Date

[Guest]

I know a little about this excel and less about programming but was directed here by a friend to try and find an answer. I have looked through the posts but can't find anything that seems to answer my question. Although since I am not exactly sure what to do, maybe this has already been answered before

I have a sheet full of data - over 20,000 rows right now and it gets longer each month. I need a macro that will pull out any row of data that has a date value between two that I select. I need this data for ad-hoc reports. I sorry if this is confusing. Basically on sheet1 in "A1" and "A2" I would put two dates. Then it would pull out any row that has a date in column A that is between these two dates and paste it on sheet2. That sheet with all the data is sheet3

If there is a way to do this that would be great as I have to pull alot of stuff out of this and this would save me alot of time sorting and searching

Thanks for taking the time to look at this and for any help that you can provide

Thanks

Randy Walker

 

[Frank Kabel]

Hi Randy
one way: Using formulas:
Assumptions:
- Column A on sheet 3 stores the dates and your dates are sorted
ascending.
- Also the date inputs in cell A1 and A2 exist in this list
- Sheet 3 consists of columns A - column D

enter the following as array formula in cells A11 (highlight all
cells prior to entering) with CTRL+SHIFT+ENTER

=IF(MATCH('sheet1'!$A$2,'sheet2'!$A$1:$A$20000,0)<=MATCH('sheet1'!$A$1,
'sheet2'!$A$1:$A$20000,0)+ROW()-1,OFFSET('sheet2'!$A$1:$D$1,MATCH('shee
t1'!$A$1,'sheet2'!$A$1:$A$20000,0)-1+ROW()-1,0),"")
copy this down

 

[Ron de Bruin]

Hi Randy

Check out Advanced filter
http://www.contextures.com/xladvfilter01.html

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

I know a little about this excel and less about programming but was directed here by a friend to try and find an answer. I

have looked through the posts but can't find anything that seems to answer my question. Although since I am not exactly sure
what to do, maybe this has already been answered before.

I have a sheet full of data - over 20,000 rows right now and it gets longer each month. I need a macro that will pull out any

row of data that has a date value between two that I select. I need this data for ad-hoc reports. I sorry if this is
confusing. Basically on sheet1 in "A1" and "A2" I would put two dates. Then it would pull out any row that has a date in
column A that is between these two dates and paste it on sheet2. That sheet with all the data is sheet3.

 

[Bob Phillips]

Hi Randy,

Here's some code that will capture all rows with dates between 9th Jan in
column A, and 3rd Feb in column B

Worksheets("Sheet2").Cells.ClearContents
With Worksheets("Sheet3")
.Range("A1").EntireRow.Insert
With .Columns("A:B")
.AutoFilter Field:=1, Criteria1:=">=09-Jan", Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="<=03-Feb", Operator:=xlAnd
End With
.Cells.SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet2").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
.Range("A1").EntireRow.Delete
.Range("A1").EntireRow.Delete
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I know a little about this excel and less about programming but was

directed here by a friend to try and find an answer. I have looked through
the posts but can't find anything that seems to answer my question.
Although since I am not exactly sure what to do, maybe this has already been
answered before.

I have a sheet full of data - over 20,000 rows right now and it gets

longer each month. I need a macro that will pull out any row of data that
has a date value between two that I select. I need this data for ad-hoc
reports. I sorry if this is confusing. Basically on sheet1 in "A1" and
"A2" I would put two dates. Then it would pull out any row that has a date
in column A that is between these two dates and paste it on sheet2. That
sheet with all the data is sheet3.

If there is a way to do this that would be great as I have to pull alot of

stuff out of this and this would save me alot of time sorting and searching.

 

[Guest]

Ok, first thanks for what you have given me so far. With alot of help I think I have this very close to being correct. I took what you gave me and got alot of help, and used a couple of books. Here is what I hav

Sub OpenMonthlyChart(
Application.ScreenUpdating = Fals
Dim FirstLetter As Strin
Dim LastLetter As Strin
Dim DateOne As Lon
Dim DateTwo As Lon

Workbooks.Open Filename:=
ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ExecutiveSummaryReports\MonthlySummary\Report.xls

Windows("MainFile.xls").Activat
Sheets("DataSheet1").Selec
FirstLetter = Range("D15").Valu
LastLetter = Range("E15").Valu
DateOne = Range("D12").Valu
DateTwo = Range("D10").Valu

Columns("P:T").Selec
Selection.ClearContent

With Windows("Report.xls"
Columns(FirstLetter & ":" & LastLetter).Selec
With Selectio
.AutoFilter Field:=1, Criteria1:=">=" & DateTwo, Operator:=xlAn
.AutoFilter Field:=1, Criteria1:="<=" & DateOne, Operator:=xlAn
End Wit
.Cells.SpecialCells(xlCellTypeVisible).Cop
Windows("TIPSData.xls").Activat
Sheets("Data1").Activat
Range("P1").Selec
ActiveSheet.Past
Application.CutCopyMode = Fals
'.Range("A1").EntireRow.Delet
End Wit

The problem is I am getting an error in the area with the "with" and the "autofilter". It is telling me something about not having any data but I know that I do. The only thing I have changed from what you provided is that the area to pull data from is set by two variables and the date ranges are set by two variables. The local computer guy helped me get this far but now even he confused. And to be honest I don't know how much he really knew to begin with (if you see this, sorry Rob, just me being frustrated)

Anyway, any further assistance you can give me would be greatly appreciated, as I am just not sure what might be wrong

Thanks

Randy Walke

----- Bob Phillips wrote: ----

Hi Randy

Here's some code that will capture all rows with dates between 9th Jan i
column A, and 3rd Feb in column

Worksheets("Sheet2").Cells.ClearContent
With Worksheets("Sheet3"
.Range("A1").EntireRow.Inser
With .Columns("A:B"
.AutoFilter Field:=1, Criteria1:=">=09-Jan", Operator:=xlAn
.AutoFilter Field:=2, Criteria1:="<=03-Feb", Operator:=xlAn
End Wit
.Cells.SpecialCells(xlCellTypeVisible).Cop
Worksheets("Sheet2").Activat
Range("A1").Selec
ActiveSheet.Past
Application.CutCopyMode = Fals
.Range("A1").EntireRow.Delet
.Range("A1").EntireRow.Delet
End Wit


--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

I know a little about this excel and less about programming but wa

directed here by a friend to try and find an answer. I have looked throug
the posts but can't find anything that seems to answer my question
Although since I am not exactly sure what to do, maybe this has already bee
answered beforelonger each month. I need a macro that will pull out any row of data tha
has a date value between two that I select. I need this data for ad-ho
reports. I sorry if this is confusing. Basically on sheet1 in "A1" an
"A2" I would put two dates. Then it would pull out any row that has a dat
in column A that is between these two dates and paste it on sheet2. Tha
sheet with all the data is sheet3

 

[Bob Phillips]

Randy,

It is very difficult to debug it without the data. Do you want to send the
workbooks to me and I will check it out. Watch out for the ant-spam filter
message in my email signature.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Ok, first thanks for what you have given me so far. With alot of help I

think I have this very close to being correct. I took what you gave me and
got alot of help, and used a couple of books. Here is what I have

Sub OpenMonthlyChart()
Application.ScreenUpdating = False
Dim FirstLetter As String
Dim LastLetter As String
Dim DateOne As Long
Dim DateTwo As Long


Workbooks.Open Filename:= _
ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ExecutiveSummaryReports\MonthlySummary\Rep
ort.xls"

Windows("MainFile.xls").Activate
Sheets("DataSheet1").Select
FirstLetter = Range("D15").Value
LastLetter = Range("E15").Value
DateOne = Range("D12").Value
DateTwo = Range("D10").Value

Columns("P:T").Select
Selection.ClearContents

With Windows("Report.xls")
Columns(FirstLetter & ":" & LastLetter).Select
With Selection
.AutoFilter Field:=1, Criteria1:=">=" & DateTwo, Operator:=xlAnd
.AutoFilter Field:=1, Criteria1:="<=" & DateOne, Operator:=xlAnd
End With
.Cells.SpecialCells(xlCellTypeVisible).Copy
Windows("TIPSData.xls").Activate
Sheets("Data1").Activate
Range("P1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'.Range("A1").EntireRow.Delete
End With

The problem is I am getting an error in the area with the "with" and the

"autofilter". It is telling me something about not having any data but I
know that I do. The only thing I have changed from what you provided is
that the area to pull data from is set by two variables and the date ranges
are set by two variables. The local computer guy helped me get this far but
now even he confused. And to be honest I don't know how much he really knew
to begin with (if you see this, sorry Rob, just me being frustrated).

Anyway, any further assistance you can give me would be greatly

appreciated, as I am just not sure what might be wrong.