move rows in excel daily

New Contributor

Hi, I am looking for a script that will take the top 100 rows from one sheet and move it to another. Ideally, I would like it to trigger automatically every 24 hours.

 

Appreciate any guidance

4 Replies

@jewels 

Should the data be moved below existing data on the other sheet, or should existing data be moved down before inserting the 100 rows?

@Hans Vogelaar below the existing data

@jewels 

If you open the workbook once a day, you could use code in the Workbook_Open event procedure in the ThisWorkbook module:

Private Sub Workbook_Open()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim r As Long
    Set w1 = Worksheets("Source")
    Set w2 = Worksheets("Target")
    r = w2.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    w1.Range("A2:A101").EntireRow.Copy Destination:=w2.Range("A" & r)
    w1.Range("A2:A101").EntireRow.Delete
End Sub

Thank you @Hans Vogelaar that worked.

Just need to find a away for it to happen once a day without me