copy & paste range of cells to another sheet based on date criteria

[Kravex]

Hi all,

I'm trying to figure a way to copy a range of cells to another sheet but only if the date is greater than the current date. I need the sheet to do this from one sheet to the next on a daily basis.

 

[Joe4]

Welcome to the Board!

Please post a sample of your data (after removing any sensitive data) so we can see the structure and have a full understanding of what needs to be copied.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, if each range supposed to be copied below the last range on the destination sheet?

 

[Kravex]

hey thanks for that, using a work computer so can't download anything sadly but pic posted below. Basically, I want the sheet to copy any data where the dep date is greater than the current day to the next sheet and post it in the same range of cells on sheet2 then doing the same on sheet2 to sheet3 so on and so forth

 

[Joe4]

OK, still a bunch of important details to clarify.

Does you data (as shown in your last post) start in cell A1?
What are the names of your data and destination sheets?

You say that you want to do this on a daily basis. So does that mean that data will be continuously added to your data sheet (cumulative, including all data from the previous day)?
Are previous data will already exist on the destination sheet too, so we need to copy to the bottom of it?
Or are we overwriting all of the existing data on the destination sheet each day?

 

[Kravex]

data in pic starts at a4
names of sheet will range from 1-30 depending on month. No data sheet as this workbook will act as a master template where I can just the month and start anew
daily basis as in, if data in dep date column is greater than current day then it will copy that data range to the next days sheet in the same range.

WIP parking.xlsx

Does office 365 have add-ins can't find the option so above is the share link

 

[Joe4]

Unfortunately, I cannot download files from my current location, so I cannot see your file.

I am still a little unclear where this data is being copied to.
Is there a sheet for each day in the file already, or does it need to be created?
How exactly are these sheets named? Is it with the full date?

 

[Kravex]

Unfortunately, I cannot download files from my current location, so I cannot see your file.

I am still a little unclear where this data is being copied to.
Is there a sheet for each day in the file already, or does it need to be created?
How exactly are these sheets named? Is it with the full date?

no sheet for each day yet as I wanted to see if this could be done before doing so. The sheets are named 1,2,3-30(31)

so pic one is current day, pic 2 is the data that I want to auto carry over. Was thing advanced filtering but that does not allow me to do multiple data ranges

 

[Joe4]

It can be done, though it is going be a bit harder to work with the data as it appears that you have three sections of data side-by-side, meaning that you have 3 different date fields on each row, and the dates may not be the same for the 4 dates on the same line. Just means that we need to loop through the 3 sections separately.

Also, it would be much easier if you had Excel 365, so you could take advantage of the new FILTER function.

 

[Kravex]

would it even be worth it, all I want to do is minimize the human factor in the data input but seems that in order to do it would be too arduous a process and just having 1 person copy and pasting after sorting would be better.

 

[Joe4]

would it even be worth it, all I want to do is minimize the human factor in the data input but seems that in order to do it would be too arduous a process and just having 1 person copy and pasting after sorting would be better.

I think automation is always preferrable to manual, in that:
- it reduces the possibility of human errors
- should save lots of time in the long run

There is a little bit of time investment initially to code it, but after that it should run much faster than doing it manually every time.
I could probably come up with the code to do it, but would want to do that from my own home computer where I can download your file (instead of trying to manually recreate it on my end).

Would you like the VBA code to create the sheet needed, as run-time, or will all the sheets for the month already be created before the Macro is run?
Note to that we might be able to incorporate this logic (whether done by VBA code or manually): How to copy data to another worksheet with Advanced Filter in Excel?