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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,378
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,595
Try:
Code:
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.
 
Last edited:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,378
Hi Mumps,
Thank you very much for this,
looks great, I'll use the name Archive then thanks for pointing it out :)
Tony
 

Forum statistics

Threads
1,084,735
Messages
5,379,503
Members
401,608
Latest member
nanana2020

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top