+ Reply to Thread
Results 1 to 6 of 6

Copy a record before deleting

  1. #1
    Shinu
    Guest

    Copy a record before deleting


    Hi,
    I am new to vba codes. I have some how manage to write the below given
    code, but it is giving me error "Application defined or object defined
    error"
    My objective is :

    To delete a record and before deleting, copy that
    record to another sheet to keep track of deleted records
    Sheets("linelist") is the dataset. Column A contains the idcode

    In Sheets("DataMangement"), cell B2 contains the value(idcode) to be
    searched in sheet linelist.

    Searh for that record in sheet linelist, once found, copy the entire
    row to another sheet ("DeletedRecords") at the last row which is empty
    and then delete that record from sheet("linelist")

    I hope so I have made my objetive clear.

    In addition I also would like a confirmation from user whether to
    delete the record or not. I have no idea of how to do that.


    My code as follows:-
    Private Sub dele1()

    Dim rng As Range
    Dim drng As Range
    Dim n As Long
    ' Dim nr As Long
    'nr = rng.Rows.Count

    srh = Worksheets("DataManagement").Range("b2")
    Sheets("LINELIST").select

    With Sheets("LINELIST")
    Set rng = .Range(.[a1], .[a1].End(xlDown))
    End With

    Set drng = rng.Cells
    drng = rng.Value

    For Each c In drng
    If srh = c Then

    drng.EntireRow.Copy


    Sheets("DeletedRecords").Select
    Range("A1").End(xlDown).Offset(1, 0).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    SkipBlanks _
    :=False, Transpose:=False

    Sheets("linelist").Select
    drng.EntireRow.delete
    End If
    Next

    Msgbox ("Idcode" & srh & "deleted from dataset")

    End Sub


    --
    ShinuPosted from - http://www.officehelp.in


  2. #2
    Tom Ogilvy
    Guest

    Re: Copy a record before deleting

    Public Sub dele1()

    Dim rng As Range
    Dim rng1 As Range
    Dim rng2 as Range
    Dim ans as Long
    Dim res as Variant
    Dim srh as Variant

    srh = Worksheets("DataManagement").Range("b2")


    With Sheets("LINELIST")
    Set rng = .Range(.Range("a1"), _
    .Range("a1").End(xlDown))
    End With

    res = Application.Match(src,rng,0)
    if iserror(res) then
    msgbox srh & " not found"
    exit sub
    End if

    set rng1 = rng(res)

    ans = Msgbox("Delete Record?", _
    "Delete this record?", vbYesNo)

    if ans = vbNo then
    exit sub
    End If

    With Sheets("DeletedRecords")
    set rng2 = .Range("A1").End(xlDown) _
    .Offset(1, 0)
    End With


    rng1.EntireRow.copy
    rng2.PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False

    rng1.EntireRow.Delete
    Msgbox ("Idcode" & srh & "deleted from dataset")

    End Sub

    --
    Regards,
    Tom Ogilvy


    "Shinu" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Hi,
    > I am new to vba codes. I have some how manage to write the below given
    > code, but it is giving me error "Application defined or object defined
    > error"
    > My objective is :
    >
    > To delete a record and before deleting, copy that
    > record to another sheet to keep track of deleted records
    > Sheets("linelist") is the dataset. Column A contains the idcode
    >
    > In Sheets("DataMangement"), cell B2 contains the value(idcode) to be
    > searched in sheet linelist.
    >
    > Searh for that record in sheet linelist, once found, copy the entire
    > row to another sheet ("DeletedRecords") at the last row which is empty
    > and then delete that record from sheet("linelist")
    >
    > I hope so I have made my objetive clear.
    >
    > In addition I also would like a confirmation from user whether to
    > delete the record or not. I have no idea of how to do that.
    >
    >
    > My code as follows:-
    > Private Sub dele1()
    >
    > Dim rng As Range
    > Dim drng As Range
    > Dim n As Long
    > ' Dim nr As Long
    > 'nr = rng.Rows.Count
    >
    > srh = Worksheets("DataManagement").Range("b2")
    > Sheets("LINELIST").select
    >
    > With Sheets("LINELIST")
    > Set rng = .Range(.[a1], .[a1].End(xlDown))
    > End With
    >
    > Set drng = rng.Cells
    > drng = rng.Value
    >
    > For Each c In drng
    > If srh = c Then
    >
    > drng.EntireRow.Copy
    >
    >
    > Sheets("DeletedRecords").Select
    > Range("A1").End(xlDown).Offset(1, 0).Select
    >
    > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    > SkipBlanks _
    > :=False, Transpose:=False
    >
    > Sheets("linelist").Select
    > drng.EntireRow.delete
    > End If
    > Next
    >
    > Msgbox ("Idcode" & srh & "deleted from dataset")
    >
    > End Sub
    >
    >
    > --
    > ShinuPosted from - http://www.officehelp.in
    >




  3. #3
    Shinu
    Guest

    Re: Copy a record before deleting


    Dear sir,
    When program reaches the below given code
    --------------------------------
    ans = MsgBox("Delete Record?", _
    "Delete this record?", vbYesNo)
    ---------------------

    it gives error showing "Run time error -'13' Type mismatch. Could you
    pls. look into this matter

    Thanks
    Shinu


    --
    ShinuPosted from - http://www.officehelp.in


  4. #4
    Shinu
    Guest

    Re: Copy a record before deleting


    Dear sir,
    Now when the program reached to the following code starting with set
    -------------------------------------
    With Sheets("DeletedRecords")
    Set rng2 = .Range("A1").End(xlDown) _
    .Offset(1, 0)
    End With
    --------------------------------
    it shows Run time error '1004' Application defined or Object defined
    error


    --
    ShinuPosted from - http://www.officehelp.in


  5. #5
    Tim Williams
    Guest

    Re: Copy a record before deleting

    Missing a period after (xlDown)

    With Sheets("DeletedRecords")
    Set rng2 = .Range("A1").End(xlDown).Offset(1, 0)
    End With

    You might want to be careful with that approach: it will fail for cases
    where the xlDown takes you to the last row and then you try to offset one
    more row... Working from the bottom up is usually safer.

    Tim



    "Shinu" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Dear sir,
    > Now when the program reached to the following code starting with set
    > -------------------------------------
    > With Sheets("DeletedRecords")
    > Set rng2 = .Range("A1").End(xlDown) _
    > Offset(1, 0)
    > End With
    > --------------------------------
    > it shows Run time error '1004' Application defined or Object defined
    > error
    >
    >
    > --
    > ShinuPosted from - http://www.officehelp.in
    >




  6. #6
    Shinu
    Guest

    Re: Copy a record before deleting


    Thanks for the help
    Shinu


    --
    Shinu
    Posted from - http://www.officehelp.in


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1