locked
How to copy and paste rows of data based on date selection criteria using VBA RRS feed

  • Question

  • Good morning,

    My name is Bill Johnston. Today I downloaded the following Excel Code VBA procedure from your website:

    https://www.extendoffice.com/documents/excel/4838-excel-copy-rows-based-on-date.html#a2
    How to copy rows and paste to another sheet based on date in Excel? Supposing, I have a range of data, now, I want to copy the entire rows based on a specific date and then paste them into another sheet.
    www.extendoffice.com

    I downloaded the data and the VBA code to my computer. I ran a test against the the Extend Office supplied VBA code using the Extend Office supplied test data. When I ran it on my Windows 10 computer, running MS Excel 2010, the result was that the VBA code did not work. Absolutely nothing happened. This VBA code is supposed to copy and paste rows of data based on date selection criteria dialog boxes. 

    If you run this VBA code against the data from ExtendOffice website, you will see what I mean i.e. nothing happens.

    I suspect the code may not be recognizing the date format, but for the life of me I am completely baffled as to why? 


    Thank you very much.

    Bill Johnston
    Sunday, February 9, 2020 7:20 PM

All replies

  • Did you select all the cells with dates in the first input box? If you select only one cell, the code won't work.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 9, 2020 7:59 PM
  • Good morning Hans and Thank You for responding.

    I tried selecting the date range and the same result of nothing happened.

    I know the VBA code was executed and there were no syntax errors or messages of any kind.

    May I please ask you if you can have a look at the VBA code, which is only 22 lines of code. 

    https://www.extendoffice.com/documents/excel/4838-excel-copy-rows-based-on-date.html#a2

    The data is only 12 rows of dates.  The creator of this code shows the date in YYYY-MM-DD format. 

    The only way to make it appear in that format is by using the custom date format feature.

    If the date was originally entered in MM-DD-YY format, then would the If statement in the code work?

    This is the part that I belive that I do not properly understand and this maybe the problem.

    Thank you.

    Bill Johnston

    Monday, February 10, 2020 8:38 AM
  • The code from the section you linked to will copy rows in which the date is on or after today and less then 5 days after today. As I am writing this, it is February 10, 2020. So only rows with dates in the range from February 10 to February 14 will be copied. All others will be ignored.

    In the example on the web page, none of the dates satisfy the criteria, so nothing will be copied. To test, I changed some of the dates:

    As you can see, two of the dates satisfy the criteria. The date format is not important, by the way.

    This is the result of running the macro:

    Only the two rows with dates between the 10th and 14th of February in this year have been copied.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, February 10, 2020 11:16 AM
  • Hello Hans,  I understand what you explained and I thank you very much for that Hans.

    1.   I did exactly as you stated. I changed all the dates and formatted all dates in the range B2:B12 as Date using the type format of: 3/14/2001

    2.   I did not change any of the code.

    3.   I did have to enable Macros in my workbook as a trusted source.

    4.   I ran the Macro CopyRow() and it prompted me to select $B$2:$B$12

    5.   It then prompted me to specify the destination  $F$2

    6.   That was the end and NOTHING happened. There was no result and no Msg of any kind.

    This is the data in my worksheet that I used in cells  A1:D12

    PROJECT DATE NAME STATUS
    AA-1001-17 2/10/2020 James Completed
    BB-1002-16 2/11/2020 John In Progress
    CC-1003-17 2/15/2020 Lucy Completed
    DD-1003-18 2/12/2020 Penny Not Started
    EE-1005-16 2/14/2020 Tom Completed
    FF-1006-18 2/15/2020 Dick In Progress
    GG-1007-17 2/16/2020 Harry In Progress
    HH-1008-16 2/17/2020 Mike Completed
    MM-1010-16 2/18/2020 Daniel No Started
    PP-1011-18 2/19/2020 Serena Completed
    SS-1004-18 2/11/2020 Bill In progress

    Monday, February 10, 2020 4:24 PM
  • I apologize Hans, but would it help if you send me your test workbook to my email ?



    Monday, February 10, 2020 4:28 PM
  • I have sent you my sample workbook. It is also available at https://www.dropbox.com/s/2gggv1wivqb30ik/foralohabill.xlsm?dl=1

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, February 10, 2020 6:23 PM
  • Hi AlohaBill,

    Forum is a public place, to protect your privacy I would delete your personal email address. Please note, do not upload your personal information, thank you for your understanding.

    In addition, this forum focuses more on general questions related to Excel desktop application, considering the issue is mainly related to VBA code, I will move the thread to Excel for Developers. You may get more helpful replies there. Thanks for your kind understanding. 

    Best Regards,

    Emily


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, February 12, 2020 1:22 AM