VBA is searching under US Date range and not UK.

  • Try it this way. I'm not sure why you are removing the Header Row for copying though.


    EDit: attachment removed and replaced with updated code below.

  • Hi Roy,


    I should of stipulated in my first post that I copied the macro from another site.

    We have a master spreadsheet that has about 20,000 rows of data on it by the end of the month. Someone has to manually filter between two dates, say 06/02/21 - 05/03/21 Highlight, copy, and paste the cells from rows A to T about 20,000 rows to a new workbook as an archive. They then remove the data from the master spreadsheet by deleting the rows.

    I would like to keep the headers, but being a totally amateur to vba, I am still learning.

    Unfortunately I can’t copy the master workbook as it’s part of a small system that user forms feed into, and all rows of data need to be accounted for by data range I.e 06/02/21 - 05/03/21 thus avoiding any duplicates.


    I hope that explains a bit more as to the scenario, apologies for not being prior forthwith.

  • I've amended the code to copy with the header rows. It also checks that the End Date is not before the Start Date

  • Hi Roy,


    I have got it working with your code thank you.

    Just a couple of points to add, is it possible to write into the exisiting code to delete the filtered rows from the source file once it's copied them to the new workbook?


    Also the column widths have not auto adjusted in the new workbook, is it possible to do that as well?


    Thanks,


    Lee

  • Try this


  • Thanks Roy,


    I just think it would be good once it's copied the selected data to the new workbook, if it could then clear the contents of the copied data then do a sort to remove the blanks from the source file and then to save it of course.


    I have inserted a sort at the start of the code as a kind of way to prep the spreadsheet prior to running the rest of the code. This puts the data in order on the new workbook. If some how i was able to run the same sort after the contents were removed then it would remove the blanks.


  • Try this


  • Carim, I’m still getting used to how the forum works. I would understand cross threading, but cross posting? ?? I started a new thread with a new subject as this thread was deviating away from my original post.

    Obviously that must be a no no, so thanks for the heads up.

    I’ve managed to use a bit of Roy’s code from his last post, and play about to get the header included. All sorted now, thanks all for the support throughout.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!