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]

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.

I am not familiar with this method of adding data.
Is the Excel file open somewhere when this happens?

The code provided does not run automatically. But we can make it run automatically based on certain actions happening.
Would having it run when the workbook is opened by sufficient?

 

[BadFish523]

I am not familiar with this method of adding data.
Is the Excel file open somewhere when this happens?

The code provided does not run automatically. But we can make it run automatically based on certain actions happening.
Would having it run when the workbook is opened by sufficient?

Man, I wish I could answer that for you. I know we do not open the sheet because if you do then that lock the Power App from adding the data into it. The sheet is basically a data holder for the app. If I open the sheet and someone else is trying to enter data from the app it will give them an error. That is why I was hoping the VBA could move this stuff without me having to open it. Can it see when a row is added to the table?

 

[Joe4]

I am pretty sure that no VBA code will run on the workbook when it is closed.
Perhaps you could have some sort of scheduling program open the file overnight when no one would be in the file, and have the VBA code run then, do the maintenance, and close itself.

If that is an option, let us know, and we can help you set that up.

 

[BadFish523]

I am pretty sure that no VBA code will run on the workbook when it is closed.
Perhaps you could have some sort of scheduling program open the file overnight when no one would be in the file, and have the VBA code run then, do the maintenance, and close itself.

If that is an option, let us know, and we can help you set that up.

Well darn. That might be my best option then. To setup a regular maintenance schedule and have the sheet opened for a brief period of time. Can you offer the code that will trigger when the sheet is opened please.

 

[Joe4]

You would use the "Application.OnTime" (which runs at a specified time) functionality with the Workbook_Open event (which is VBA code that runs automatically when a workbook is open).
Here is a write-up and sample code on that: Run a Macro at a Set Time : Microsoft Excel

Then, you would just add code to the end of the "Demo" code that saves and closes the file.

See how far you get with that, and we can help you with any issues thay you may encounter.

 

[BadFish523]

I'm not having much luck. I tried to go simple and just get the given code to run when I open the workbook. Can't get it do even do that. Here is the code that I tried.

Sub Workbook_Open()
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]

Did you put the code in the "ThisWorkbook" module?
Event procedures (which are VBA code that runs automatically) MUST be placed in the proper procedures in order to run automatically.
"Workbook_Open" event procedures go in the "ThisWorkbook" module.

One way to verify if the code is firing is by simply adding a message box to it, i.e.

Sub Workbook_Open()
    MsgBox "Code is running"
    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

If you do not get the MsgBox, then the code is not running, meaning either you placed it in the wrong module, or macros/VBA are disabled.
If you get the MsgBox, but nothing else happens, then there is an issue with the code (maybe not referencing the correct sheet or object).

 

[BadFish523]

Did you put the code in the "ThisWorkbook" module?
Event procedures (which are VBA code that runs automatically) MUST be placed in the proper procedures in order to run automatically.
"Workbook_Open" event procedures go in the "ThisWorkbook" module.

One way to verify if the code is firing is by simply adding a message box to it, i.e.

Sub Workbook_Open()
    MsgBox "Code is running"
    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

If you do not get the MsgBox, then the code is not running, meaning either you placed it in the wrong module, or macros/VBA are disabled.
If you get the MsgBox, but nothing else happens, then there is an issue with the code (maybe not referencing the correct sheet or object).

I thought I did but apparently I did not. It works now. Thank you both!

 

[Joe4]

You are welcome!

Note that if you plan to use it via a Scheduler, you may still want to do a few things:
1. Add an "Application.OnTime" line of code to restrict it to only running at a certain time (if you do not want it to run every time someone opens it).
2. Add code to automatically save and close the file, so the Scheduler does not leave it open after opening it.

 

[Rip1971]

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