move rows of data from 1 sheet to another based on a date

dfaldyn

New Member
Joined
Aug 23, 2007
Messages
4
I'm trying to move a row within a range of cells from 1 sheet to another, then delete that information off the original sheet. The criteria for moving the row or not is based on a date. I want anything older than 7 days from today to be moved to a history page and removed from the current page. This is updated daily.

I've gotten it to basically work except for some reason it will not move a date with the 7th day of the month in it. It appears to work because it's no longer on the current page, however, it is also not on the history page.

Below is the code I'm using.

Code:
Dim Today
Today = Now    ' Assign current system date and time.

    DATA = Sheets("Current Orders In Process").Range("o4:o100")
    CROW = 4
    For Each RDATA In DATA
    If RDATA <> 0 And RDATA < Now() - 7 Then
    Rows(CROW & ":" & CROW).Select
    Selection.Copy
    Sheets("History - Past Orders").Select
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown
    Sheets("Current Orders In Process").Select
    Rows(CROW & ":" & CROW).ClearContents
    End If
    CROW = CROW + 1
    Next

What happens with this is when the date is say 8/7/07, it deletes it from current but it does not copy it to the history.

There are also additional rows in the sheet that do not have dates in the column yet that I do not want moved.  That's why I have the does not equal 0 part in the code.

I'm fairly new to code and just have not been able to figure out what's wrong.

Thanks for your help
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi dfaldyn,

I tried your code and didn't experience the problem with the 7th day of the month. But perhaps you will find this helpful. I re-coded your problem based on one-line cut and paste rather than copy and clear. That way, if the paste fails, the cut should as well and there should never be a row removed without being inserted in the history sheet.

Code:
   Dim Cell          As Range
   Dim NextRow       As Long
   Dim ArchiveDate   As Date

   ArchiveDate = Now() - 7
   With Sheets("History - Past Orders")
      'find next available row on history sheet
      NextRow = .Range("O65536").End(xlUp).Row + 1
      
      For Each Cell In Sheets("Current Orders In Process").Range("O4:O100")
         If Cell.Value <> 0 And Cell.Value < ArchiveDate Then
            NextRow = NextRow + 1
            Cell.EntireRow.Cut Destination:=.Rows(NextRow)
         End If
      Next Cell
      
   End With

Keep Excelling.

Damon
 
Upvote 0
Thanks for the reply.

Yours seemed to work, however, it skips the first line and leaves it on the "current orders.." page. Also, I have conditional formatting set on the "Current ... " page and when you cut, it turns that off those lines.

I tried my formula again since you said it worked ok on yours but here's what I found.

I ran a test on the following dates:

1/1/07, 1/15/07, 2/7/07, 7/7/07, and 8/7/07.

It correctly copied all except the 1st one. (Which is also what it was doing with your formula)

Now with your formula it at least stayed on the "Current" sheet, however with mine it deletes because the way I clear the contents.

I'm still trying to figure out, any additional insight would be appreciated.
 
Upvote 0
Hi dfaldyn,

I'm wondering if the problem with 1/1/07 is occurring because it resides in row 1, 2 or 3, which the code is designed to ignore. If you want the code to look at these rows you need to change the Range("O4:O100") reference.

Also, you can convert my code to copy and clear by changing

Cell.EntireRow.Cut Destination:=.Rows(NextRow)

to

Cell.EntireRow.Copy Destination:=.Rows(NextRow)
Cell.EntireRow.ClearContents

which should leave your conditional formatting (as well as the entire row) in place. I had assumed (wrongly) that you wanted the row removed.

Perhaps this will help.

Damon
 
Upvote 0
Damon,

That worked GREAT! Thanks so much. However, I failed to tell you that on the history page I have a Data Range Named and also totals at the bottom. But I decided to move the totals to the top and have the named range go to row 60000. So, it works great.

I really appreciate all your help!
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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