[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:
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 thisSub 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 -
Hello everyone,
How are you CCM81? ;o)
Copying an entire row is useless: what is the last column used in "dashboard"?
Are there multiple "completes" to transfer at once or just one?
--
Michel
