Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Copy to another sheet based on date?

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy to another sheet based on date?

    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

  2. #2
    New Member
    Join Date
    Oct 2009
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy to another sheet based on date?

    Anyone ? :D

  3. #3
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy to another sheet based on date?

    Quote Originally Posted by Kevie192 View Post
    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
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  4. #4
    Board Regular
    Join Date
    Mar 2007
    Location
    Chicago Area
    Posts
    2,639
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy to another sheet based on date?

    Hi Kevin,

    If your sheets look like this:

    Data
    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
    Excel 2003



    1
    AB
    1StartEnd
    24/1/20095/1/2009
    3
    4DateOther Data
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •