Forum Discussion
Move entire row to another sheet when a cell is filled
Hello, I'm trying to have the excel to work as in subject, I found online a solution and I tried to compile it but it's not working. I want the rows of every sheet except the last one to move to the sheet "RICONSEGNATE" when the column "DATA RICONSEGNA FORNITORE" is filled with any date. Can you please help me to figure it out?
I'd be happy to help you set up an Excel macro that automatically moves entire rows to another sheet when a cell in "DATA RICONSEGNA FORNITORE" is filled with any date, excluding the last sheet. Here's the code along with explanations:
VBASub MoveRowsWhenFilled()
' Declare variables
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim cellValue As Variant' Define the target sheet name
Set targetSheet = Sheets("RICONSEGNATE")' Loop through all worksheets except the last one
For Each sourceSheet In Sheets
If sourceSheet.Index <> Sheets.Count Then ' Skip the last sheet' Find the last row of data in the source sheet
lastRow = sourceSheet.Cells(Rows.Count, 1).End(xlUp).Row' Loop through each row in the source sheet
For i = 2 To lastRow ' Start from row 2 to avoid headers' Check if the cell in "DATA RICONSEGNA FORNITORE" is filled with a date
cellValue = sourceSheet.Cells(i, "DATA RICONSEGNA FORNITORE").ValueIf IsDate(cellValue) Then ' Check if it's a valid date
' Move the entire row to the target sheet
sourceSheet.Rows(i).EntireRow.Cut
targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Offset(1).EntireRow.InsertEnd If
Next i
End If
Next sourceSheet
End Sub
Explanation:
- Declare variables: sourceSheet and targetSheet hold worksheet objects, lastRow stores the last row of data, and cellValue holds the value of the checked cell.
- Target sheet: Set the target sheet to "RICONSEGNATE".
- Loop through sheets: The For Each loop iterates through all worksheets except the last one.
- Find last row: lastRow is determined using End(xlUp).
- Loop through rows: Another For loop iterates from row 2 (skipping headers) to lastRow.
- Check cell value: IsDate checks if the cell value is a valid date.
- Move row if date: If it's a date, the entire row is cut and inserted into the target sheet below the last row.
How to use:
- Save the code in a VBA module (Insert > Module).
- Place the macro in your workbook where you want it to run automatically.
- Assign a keyboard shortcut or button to the macro for manual execution.
To run manually:
- Select any cell in your workbook.
- Run the macro using your assigned shortcut or button.
Important notes:
- This code assumes "DATA RICONSEGNA FORNITORE" is in the same column in all sheets. Adjust the column index if needed.
- Consider adding error handling if the target sheet doesn't exist or other potential issues arise.
- Test the code thoroughly with different scenarios before using it on your actual data.
I hope this refined code effectively addresses your requirement and provides a clear solution!
I tried to figure it out but I couldn't make it work. Could you please set it up? Consider that for the sheets "MISCELA NUCLEARE -AZOTO" and "GAS CAMPIONE" the column "DATA RICONSEGNA FORNITORE" is M and not K. You can find the file in attachment. Thanks in advance.
I can still help you with the formula based on the information you provided. Please share the following details:
- Formula Location: In which specific sheet and cell is the current formula located?
- Error Message: What specific error message are you encountering when using the formula?
- Sample Data: Can you share a few sample rows from the "MISCELA NUCLEARE -AZOTO" and "GAS CAMPIONE" sheets, including the values in columns G and M? This will help me understand your data structure and identify potential issues.
With this information, I can diagnose the problem and provide a revised formula that works correctly for all sheets, taking into account the different column positions for "DATA RICONSEGNA FORNITORE".
Additionally, if you have any specific requirements or preferences for the output of the formula, feel free to share them as well. The more information you can provide, the better I can assist you.
I tried to attach an Excel file to help with your Excel sheet.
