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

 

[Joe4]

I am trying to do the same thing but i get an error when code is run.
Run-time error '9'
Subscript out of range

any suggestion for me to achieve same as original poster

What is your code and which line is highlighted when you select the "debug" option?

 

[Rip1971]

What is your code and which line is highlighted when you select the "debug" option?

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

With Sheets("Data").ListObjects(1).DataBodyRange.Rows this one is highlighted

 

[Joe4]

Is your data sheet named "Data", and is the table the first object on the sheet?
What exactly is the name of your table?
Are you already on the "Data" sheet when you trying running the code?

 

[Rip1971]

Is your data sheet named "Data", and is the table the first object on the sheet?
What exactly is the name of your table?
Are you already on the "Data" sheet when you trying running the code?

Well i feel a bit silly now. but these questions have helped solve the problem.
I forgot the step to have my data as a table. it was just data. after putting it in table format it worked.

Thanks for asking the right question.

 

[Joe4]

You are welcome!
Glad I was able to help.