Copy current row to next empty row on new sheet.

[primagic]

I am trying to copy the current row to a new blank row on another sheet after selecting a value from a validation list (dropdown list)

I have the following code tested working from a command button and it works, but I want it to be if the value selected equals 'Approved for archive' then copy else don't copy.

Private Sub CommandButton1_Click()

Dim CurrentRow As Integer
CurrentRow = ActiveWindow.RangeSelection.Row
Rows(CurrentRow).Select      'This copies the selected row
Selection.Copy
Sheets("Archived").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Also is there any way of removing it from the 'Archived' sheet if the value has been changed which does not equal 'Approved for archive'?

 

[SkipVought]

hi,

I am trying to copy the current row to a new blank row on another sheet after selecting a value from a validation list (dropdown list)

from a command button

Forum707 is a better place to get VBA questions answered.

Use the Worksheet_Change event, rather than a command button.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        
        If .Value = "Approved for archive" Then
            .EntireRow.Copy
            
            Sheets("Archived").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
            Paste:=xlPasteValues
        End If
    End With
End Sub

Also is there any way of removing it from the 'Archived' sheet if the value has been changed which does not equal 'Approved for archive'?

???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}