Forum Discussion
How to automatically move rows from one sheet to another in Excel and populate newest at top
Hello,
I currently have an Excel spreadsheet and code to automatically move a row when I change the status in a dropdown to "completed" to move the row from Sheet1 into the Completed Sheet.
The problem is that the completed items start at the top of the Completed Sheet and go down from oldest to newest when I would like the completed items to have the newest at the top going to oldest of the Completed Sheet. Is there a way to update my code, or add a code to accomplish this?
LenaGonzo Rather than using a macro, why not add a status column to your data, convert your data range to a table (Insert, Table) and then add a slicer to your table for the Status column. That makes it very easy to filter for the status.
- Hi Jan Karel Pieterse, Will that move the completed rows from Sheet1 to the separate "completed" worksheet?
LenaGonzo No, it changes your current layout as it obsoletes the completed worksheet. I just wanted to make you look at this from a different perspective.
The image you sent appears to show code related to moving rows based on a dropdown value in Excel. Here's how you can modify your code to populate the "Completed" sheet with the newest entries at the top:
Option 1: Using Insert Rows and MoveRow (simpler method):
VBASub MoveRowsToCompleted() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim lastRow As Long Dim i As Long ' Set source and target sheets Set sourceSheet = Worksheets("Sheet1") Set targetSheet = Worksheets("Completed") ' Get the last row in the target sheet (assuming no headers) lastRow = targetSheet.UsedRange.Rows.Count ' Loop through rows in the source sheet starting from the bottom For i = sourceSheet.UsedRange.Rows.Count To 2 Step -1 ' Check if the dropdown value in column B is "completed" If sourceSheet.Cells(i, 2).Value = "completed" Then ' Insert a new row at the top of the target sheet targetSheet.Rows(1).Insert xlUp ' Move the row from the source sheet to the target sheet sourceSheet.Rows(i).Move targetSheet.Rows(1) ' Decrement the counter to avoid skipping rows i = i - 1 End If Next i End SubExplanation:
- This code iterates through the rows of the source sheet in reverse order (from bottom to top) using a loop.
- It checks if the value in column B (assuming your dropdown is in column B) is "completed".
- If the condition is met, it inserts a new row at the top of the target sheet using Insert Rows.
- Then, it moves the entire row from the source sheet to the newly inserted row in the target sheet using MoveRow.
- Decrementing the counter (i) by 1 ensures that skipped rows are not encountered during the loop.
Option 2: Using CopyRows and Sort (more efficient for large datasets):
VBASub MoveRowsToCompleted() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim lastRow As Long ' Set source and target sheets Set sourceSheet = Worksheets("Sheet1") Set targetSheet = Worksheets("Completed") ' Get the last row in the target sheet (assuming no headers) lastRow = targetSheet.UsedRange.Rows.Count ' Copy all rows from the source sheet that meet the criteria Dim filteredRange As Range Set filteredRange = sourceSheet.UsedRange.Find(What:="completed", LookIn:=xlValues, SearchOrder:=xlByRows) If Not filteredRange Is Nothing Then filteredRange.Copy targetSheet.Rows(lastRow + 1) End If ' Sort the target sheet based on a column indicating completion date (adjust column number as needed) targetSheet.UsedRange.Sort key1:=targetSheet.Columns("C"), SortOrder:=xlDescending ' Assuming completion date is in column C End SubExplanation:
- This code first finds all rows in the source sheet where the value in column B is "completed" using the Find method.
- If any rows are found, it copies them to the bottom of the target sheet using CopyRows.
- Finally, it sorts the entire target sheet based on a column representing the completion date (assuming column C) in descending order using Sort. This ensures the newest entries are at the top.
Choose the option that best suits your needs and coding preferences. Remember to adjust the code to match your specific sheet names, column references, and any other relevant modifications
- Hi smylbugti222gmailcom, I tried using both of those codes but they did not work for me. Do I need to remove the code that I have under "Modules?" If I do that, I get an error for my code under "Worksheet."
