Hi Guys,
I am currently trying to archive data from a worksheet that is in continual use.
What I would like to happen is that when an action is listed as Complete then that row from the worksheet is moved into the next available row of a new one, this is the current VBA I am attempting to use but I keep being met with the error "Run-time error '1004': Application-defined or object-defined error".
Sub Archive()
Dim r As Long, endRow As Long, pasteRowIndex As Long
endRow = 10 '
pasteRowIndex = 1
For r = 1 To endRow
If Cells(r, Columns("E").Column).Value = "Complete" Then
Rows(r).Select
Selection.Copy
Sheets("Archive").Select
Rows(pasteRowIndex).Select
ActiveSheet.Paste
pasteRowIndex = pasteRowIndex + 1
Sheets("Queries").Select
End If
Next r
End Sub
I should also mention that the description Complete is one criterion of a drop down menu.
I would be grateful if somebody could please identify where I am going wrong with this.
Thanks
I am currently trying to archive data from a worksheet that is in continual use.
What I would like to happen is that when an action is listed as Complete then that row from the worksheet is moved into the next available row of a new one, this is the current VBA I am attempting to use but I keep being met with the error "Run-time error '1004': Application-defined or object-defined error".
Sub Archive()
Dim r As Long, endRow As Long, pasteRowIndex As Long
endRow = 10 '
pasteRowIndex = 1
For r = 1 To endRow
If Cells(r, Columns("E").Column).Value = "Complete" Then
Rows(r).Select
Selection.Copy
Sheets("Archive").Select
Rows(pasteRowIndex).Select
ActiveSheet.Paste
pasteRowIndex = pasteRowIndex + 1
Sheets("Queries").Select
End If
Next r
End Sub
I should also mention that the description Complete is one criterion of a drop down menu.
I would be grateful if somebody could please identify where I am going wrong with this.
Thanks
Last edited: