@LenaGonzo
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 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):
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 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
