Using Dates to Move Completed Projects to Another Sheet

[Hawksfand]

Hi!
I've tried for a couple hours to figure this one out on my own and just can't seem to get the right combination going.

I need a macro that will look down a column of dates (and blank spaces for items that are not yet completed) and take out the ones that are older than a week and put them into another sheet with the exact same table specifications. The sheet names are "Display" and "Completed" and the row should be deleted after this move. The column is H and it starts in row 3. If a lot of notes could be added to explain what is going on in the code that would be great!

Let me know if you need more details, or if something doesn't make sense
Thanks!

 

[JLGWhiz]

It is assumed that values in column H equate to a full date (eg. 5/18/2015) and are formatted as Date.
The designation of which sheet name applied to what was unclear, so you can replace the sheet index number in the set statements with the names where you see 'Edit sheet name'.

Sub moveCompl()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long
Set sh1 = Sheets(1) 'Ecit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, "H").End(xlUp).Row 'find last row with data in column H
    For i = lr To 3 Step -1 'Go from bottom up for row deletions on sh1
        If Date - sh1.Cells(i, "H").Value > 7 Then 'Check criteria
            sh2.Rows(3).Insert 'use inert method to keep in same order as sh1
            sh1.Rows(i).Copy
            sh2.Range("A3").PasteSpecial xlPasteAll 'use paste special to get formatting.
            sh1.Rows(i).Delete
        End If
    Next
End Sub

Be sure to enclose your sheet names in quotation marks inside the parentheses.

 

[Hawksfand]

It is assumed that values in column H equate to a full date (eg. 5/18/2015) and are formatted as Date.
The designation of which sheet name applied to what was unclear, so you can replace the sheet index number in the set statements with the names where you see 'Edit sheet name'.

Sub moveCompl()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long
Set sh1 = Sheets(1) 'Ecit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, "H").End(xlUp).Row 'find last row with data in column H
    For i = lr To 3 Step -1 'Go from bottom up for row deletions on sh1
        If Date - sh1.Cells(i, "H").Value > 7 Then 'Check criteria
            sh2.Rows(3).Insert 'use inert method to keep in same order as sh1
            sh1.Rows(i).Copy
            sh2.Range("A3").PasteSpecial xlPasteAll 'use paste special to get formatting.
            sh1.Rows(i).Delete
        End If
    Next
End Sub

Be sure to enclose your sheet names in quotation marks inside the parentheses.

So I tried it out and at the moment it isn't erroring, but it also isn't giving me any sort of result (nothing is being deleted or moved or anything). I've looked over the code to see if I could solve it but it's over my head

Sub moveCompl()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long
Set sh1 = Sheets("Display") 'Ecit sheet name
Set sh2 = Sheets("Completed") 'Edit sheet name
lr = sh1.Cells(Rows.Count, "H").End(xlUp).Row 'find last row with data in column H
    For i = lr To 3 Step -1 'Go from bottom up for row deletions on sh1
        If Date - sh1.Cells(i, "H").Value > 7 Then 'Check criteria
            sh2.Rows(3).Insert 'use inert method to keep in same order as sh1
            sh1.Rows(i).Copy
            sh2.Range("A3").PasteSpecial xlPasteAll 'use paste special to get formatting.
            sh1.Rows(i).Delete
        End If
    Next
End Sub

 

[JLGWhiz]

The code should be copied to the standard code module 1. Try reversing your sheet names for sheets 1 and 2. My test set up used sheet 1 for the sheet to search column H and sheet 2 as the destination sheet for the copied data. The code ran without error and produced the desired results for me.

 

[Smitty]

Welcome to the Board!

If you keep all of your data on one sheet you can use Pivot Tables to summarize Complete/Open items.

 

[Hawksfand]

So it works and I'm just an idiot...I put in the 15th as the date completed and naturally it found nothing older than a week...
Thanks so much for your help!

@Smitty:
I would do that but we want to display the main sheet so we don't want to have other things on there cluttering it up.
Thanks though!

 

[JLGWhiz]

You're welcome,
Regards, JLG