Forum Discussion
LenaGonzo
Feb 21, 2024Copper Contributor
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. ...
smylbugti222gmailcom
Feb 22, 2024Iron Contributor
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):
VBA
Sub 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 Sub
Explanation:
- 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):
VBA
Sub 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 Sub
Explanation:
- 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
LenaGonzo
Feb 22, 2024Copper Contributor
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."