VBA to move rows

[BadFish523]

Hello,

I have a sheet with a table in it. What I'm wanting to do is when the row count reaches 1750 I would like to take the top 1000 rows (not the header row) and move them to the first empty row on another sheet. I am basically trying to keep only a 1000 rows of data and then add the others to an Archive sheet. I need the rows on the data sheet that were copied over to be removed from the data sheet after they are copied over so the last rows will move up to become the new first rows of data if that makes sense?

Hope I didn't make it too confusing,
Stephen

 

[BadFish523]

Nobody?

 

[Joe4]

Is the data on the archival sheet actually in actual "table" form too (note that tables are a little different than simple lists of data, and have different properties), or is it just a list of the data rows (so in addition to moving data over, we will need to grow/shrink table sizes)?

 

[BadFish523]

Is the data on the archival sheet actually in actual "table" form too (note that tables are a little different than simple lists of data, and have different properties), or is it just a list of the data rows (so in addition to moving data over, we will need to grow/shrink table sizes)?

The archive sheet does not have a table. I can have it be a table or not. Whichever is easier. The data sheet does have an actual table and that needs to remain a table. Thanks for the reply!

 

[BadFish523]

@Joe4 Edit: guess I can't share the sheet here. Sorry, it is an excel online sheet in my OneDrive if that makes a difference.

 

[BadFish523]

Made a copy to my personal OneDrive. Book.xlsx

 

[Marc L]

Hi, according to your attachment a VBA demonstration as a beginner starter :​

Sub Demo1()
    With Sheets("Data").ListObjects(1).DataBodyRange.Rows
        If .Count >= 1750 Then
           .Item("1:1000").Copy Sheets("Archive").Cells(Rows.Count, 1).End(xlUp)(2)
           .Item("1:1000").EntireRow.Delete
        End If
    End With
End Sub

 

[Joe4]

Sorry, I have been wrapped up in morning meetings.
See if you can get the code that Marc came up to work for you. You may just need to make some adjustments (i.e. for sheet names).

 

[BadFish523]

@Marc L is there something I need to do to trigger this? I added the code, then saved. Closed and opened, edited current rows. Added a new row to the table. None of these made any changes both in the desktop or after I uploaded it to the OneDrive.

 

[BadFish523]

More details if this helps. The data is added by a Power App using the Patch() function. I would like the VBA to be triggered when the new row gets added that puts the row count at or over 1750. Thank you all for the time so far.