Remove data from one sheet and add it to another according to date

[tonywatsonhelp]

Hi Everyone,

I'm need a macro to help me remove events that are past a date i choose so i can "Arcive" them.

so heres what I need

Sheet "Diary" holds the data i want to move

Sheet "History" is where i want it to go

Sheet "Diary" Column A has the event dates
Sheet "History" Cell A1 has the date

So I want the macro when run to
Look at the date in

Sheet "History" Cell A1

goto

Sheet "Diary" Column A and find all dates that are before that date copy those rows Columns A to H only to sheet History starting at first empty row in Column H

then delete the entire rows of the columns copied in "Diary"

hope that makes sense? please help if you can

Thanks

Tony ​

_{^{<strike>
</strike>}}​

 

[mumps]

Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Diary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Sheets("Diary")
        .Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:="<" & Sheets("Archive").Range("A1")
        .Range("A2:H" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "H").End(xlUp).Offset(1, 0)
        .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

I used "Archive" instead of "History" to name the destination sheet. "History" is a reserved name so Excel won't let me use it to name the sheet.

 

[tonywatsonhelp]

Hi Mumps,
Thank you very much for this,
looks great, I'll use the name Archive then thanks for pointing it out
Tony

 

[mumps]

You are very welcome.