[Excel 2016] Archive a row based on condition
ouska36
Posted messages
3
Status
Member
-
michel_m Posted messages 18903 Registration date Status Contributor Last intervention -
michel_m Posted messages 18903 Registration date Status Contributor Last intervention -
```html
Hello everyone,
I went through the forum looking for a solution to copy/paste a row from the "Dashboard" tab to the "Archives" tab when the value of column "AD" equals "COMPLETE" (Excel 2016). The macro ends up deleting the archived row from the "Dashboard" tab.
The solution I found (thanks to the contributors) does the job up to the pasting... the macro pastes the row at the top and overwrites the previously archived one...
What I want is for the paste to enter the "Archives" table starting from row 7 and not overwrite the already archived rows (ability to archive multiple rows at the same time): Either by adding the rows one below the other or, if it's not too complicated, inserting the new archived rows at the top of the table and shifting the old ones down.
Can you please help me correct "my" macro (I am not a beginner at all, I'm learning as I go):
Code:
Thank you for your help.
Best regards ```
I went through the forum looking for a solution to copy/paste a row from the "Dashboard" tab to the "Archives" tab when the value of column "AD" equals "COMPLETE" (Excel 2016). The macro ends up deleting the archived row from the "Dashboard" tab.
The solution I found (thanks to the contributors) does the job up to the pasting... the macro pastes the row at the top and overwrites the previously archived one...
What I want is for the paste to enter the "Archives" table starting from row 7 and not overwrite the already archived rows (ability to archive multiple rows at the same time): Either by adding the rows one below the other or, if it's not too complicated, inserting the new archived rows at the top of the table and shifting the old ones down.
Can you please help me correct "my" macro (I am not a beginner at all, I'm learning as I go):
Code:
Sub Groupe13_Cliquer()
A = Worksheets("Dashboard").Cells(Rows.Count, 2).End(xlUp).Row
For i = A To 2 Step -1
If Worksheets("Dashboard").Cells(i, 30).Value = "COMPLETE" Then
Worksheets("Dashboard").Rows(i).copy Worksheets("Archives").Cells(Rows.Count, 1).End(xlUp)
Worksheets("Dashboard").Rows(i).EntireRow.Delete
End If
Next
End Sub
Thank you for your help.
Best regards ```
3 answers
Hello michel_m, hello ccm81,
Thank you for your help.
I was suggested the following solution on another forum that works very well:
The only issue now is that the pasting of the cells occurs outside the table when the table is full:
Thank you for your help.
I was suggested the following solution on another forum that works very well:
Sub Groupe13_Click()
Dim DashRow As Long
Dim ArchiveRow As Long
Dim i As Long
Dim range As Range
DashRow = Worksheets("Dashboard").Cells(Rows.Count, 2).End(xlUp).Row
For i = DashRow To 7 Step -1
If Worksheets("Dashboard").Cells(i, 30).Value = "COMPLETE" Then
Set range = Worksheets("Archives").Range("B6:B" & Cells(Rows.Count, 2).End(xlUp).Row)
ArchiveRow = range.SpecialCells(xlCellTypeBlanks).Cells(1, 1).Row ' Checks the target row number
Worksheets("Dashboard").Rows(i).Copy
Worksheets("Archives").Cells(ArchiveRow, 1).PasteSpecial
Worksheets("Dashboard").Rows(i).EntireRow.Delete
End If
Next
End Sub
The only issue now is that the pasting of the cells occurs outside the table when the table is full:
Hello
Try this
Best regards
Try this
Sub Groupe13_Click() A = Worksheets("Dashboard").Cells(Rows.Count, 2).End(xlUp).Row For i = A To 2 Step -1 If Worksheets("Dashboard").Cells(i, 30).Value = "COMPLETE" Then Worksheets("Dashboard").Rows(i).Copy Worksheets("Archives").Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) Worksheets("Dashboard").Rows(i).EntireRow.Delete End If Next End Sub Best regards
and if necessary, respond to what I asked you
and apparently, unlike the other forum, you didn’t provide your binder....