Copy to another sheet based on date?

Kevie192

New Member
Joined
Oct 2, 2009
Messages
17
Hi all,

I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!

I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.

I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)

Any help you can give would be much appreciated.

Cheers,

Kevin
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Anyone ? :D

You're bumping your thread after 50 minutes without reply? Hmmm.

Anyway, have you tried the Advanced filter? It allows you to filter data based on criteria. Here, the criteria will be: "lie between the 2 dates".

Wigi
 
Upvote 0
Hi Kevin,

If your sheets look like this:

Excel Workbook
AB
1DateOther Data
21/1/20091
32/1/20092
43/1/20093
54/1/20094
65/1/20095
76/1/20096
87/1/20097
98/1/20098
109/1/20099
1110/1/200910
1211/1/200911
1312/1/200912
Data
Excel Workbook
AB
1StartEnd
24/1/20095/1/2009
3
4DateOther Data
Excel 2003 1
Excel 2003



...then maybe this code on sheet '1' would do what you are after:

Code:
Option Explicit
Private Sub CommandButton1_Click()
Dim rngData As Range, lngStart As Long, lngEnd As Long
Range("A5:A" & Rows.Count).EntireRow.ClearContents
If IsDate(Range("A2")) Then
    lngStart = [A2]
Else
    lngStart = 0
End If
If IsDate(Range("B2")) Then
    lngEnd = [B2]
Else
    lngEnd = 99999
End If
If lngEnd< lngStart Then
    MsgBox "Start date must be after end date.  Please try again.", vbCritical, "Error!"
    Exit Sub
End If
Sheets("Data").AutoFilterMode = False
With Sheets("Data")
    Set rngData = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "B").End(xlUp))
End With
rngData.AutoFilter Field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, _
        Criteria2:="<=" & lngEnd
rngData.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Range("A4")
End Sub

...alternatively, someone else post the advanced filter method and I'll probably never do it this way again :)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top