Cut and copy Records in another sheet.

Hello everyone,

I have a issue here with a code. This code erase the record but what I want is to cut and copy the record in another sheet. I have tried but nothing till the moment. Sure you can help me.

'declare the variables
    Dim findvalue As Range, DelR As Range
    Dim cDelete As VbMsgBoxResult
    Dim cNum As Integer
    Dim FirstAddress As String
    Dim nextrow As Range
    
TransferTableFromAccess

'check for values
    If Reg1.Value = "" Or Reg9.Value = "" Then
        MsgBox "There is not data to delete.", vbExclamation, "Training Database"
        Exit Sub
    End If
'give the user a chance to change their mind
    cDelete = MsgBox("You are about to delete this employee and all of their training.", vbYesNo + vbDefaultButton2 + vbQuestion, "Training Database")
    If cDelete = vbYes Then
'find the first value
        Set findvalue = Sheets("Data").Range("C:C").Find(What:=Reg0, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
            Set DelR = findvalue
            FirstAddress = findvalue.Address
            Do
                Set findvalue = Sheets("Data").Range("C:C").FindNext(findvalue)
                Set DelR = Union(DelR, findvalue)
            Loop While FirstAddress <> findvalue.Address
        End If
        If Not DelR Is Nothing Then DelR.EntireRow.delete
    End If
'clear the controls
    cNum = 9
    For X = 0 To cNum
        Me.Controls("Reg" & X).Value = ""
    Next
'run the filter
    AdvFilter
'refresh the listbox
    lstLookup.RowSource = ""
    lstLookup.RowSource = "Filter_Staff"

delete
PushTableToAccess

Thanks everyone
Edward

hi Edward, if you meant "cut and paste" then change this:

If Not DelR Is Nothing Then DelR.EntireRow.delete

to

If Not DelR Is Nothing Then
    DelR.EntireRow.Copy Sheets.Add.Range("a1")
    DelR.EntireRow.Delete
End If

Thanks watersev,

I am using now a nextrow for an expandable list and I am getting the error 438. I am not used to this code.

        Set nextrow = Sheets("DataOld").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)

        If Not DelR Is Nothing Then
            DelR.EntireRow.Copy Sheets.Add.nextrow <--- here is the error
            DelR.EntireRow.delete
        End If

What do think is the correct structure?

Thanks

DelR.EntireRow.Copy nextrow

The thing is that nextrow and DelR dont have the same area and I am getting the error 1004 with that code.

try this out:

DelR.EntireRow.Copy nextrow.EntireRow

Thanks! Thats perfect!