Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Unique Record | Excel Discussion (Misc queries) | |||
Copy and paste from last record at bottom of column | New Users to Excel | |||
Deleting a record from Access data base using programming from excel | Excel Programming | |||
record macro - copy absolute, paste relative | Excel Programming | |||
how to copy record only with amount from 1 sheet to another | Excel Programming |