Discussion Article

Subject:
Copy a record to another sheet before deletion
Name:
Shinu
Email (spam proof):

Email the originator of this post
Date:
Saturday, February 04, 2006
Hits:
68
Discussion (Terms and Conditions):

Hi there,
 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 my objetive clear.

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

Sponsored by the best FTP application on the internet: