Hi everybody,

I have a sheet contains lots of records, some of these records are old
and not needed , so I want to transfer them to another sheet in order
to go back to them in the future.

I would like you please to provide me with a VBA code works on moving
the whole record to the other sheet when I select any cell of that
record

I hope that my question is clear for you,

thank you,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=497490

Hi
Here is piece of VB code to solve ur problem. Hope you are able to put
this code in your file and run it.

Just copy paste the following code in your workbook module in Visual
Basic Editor available under ToolsMacros Visual Basic Editor.

Now go to the sheet that contains the data and double click any cell in
your old record (I have assumed that you have one record in one row).
This record is cut from this sheet and pasted in the next sheet.
Please note that this code will suppress the double click function in
this excel file of yours. Also make sure that the sheet following your
data sheet is a new sheet; as it's this new sheet where old records
will be dumped. And before you start take a backup of ur original file.


Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
Rows(ActiveCell.Row).Select
Selection.Cut
ActiveSheet.Next.Select
ActiveSheet.UsedRange.SpecialCells(xlLastCell).Sel ect
Row = ActiveCell.Row + 1
Range("A" & Row).Select
ActiveSheet.Paste
Range("A" & Row).Select
Application.CutCopyMode = False
ActiveSheet.Previous.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
End Sub


Thanks Karthik Bhat

Thank you for ur reply,

I tested the code you delivered but it stops at the following line:

Range("A" & Row).Select

On another hand, I think the code you delivered works on cutting the
whole row and paste it in another sheet, while I want the code to copy
the current record and paste it in the other sheet then clearing the
contents of the copied record in order for me re-fill it again.

I hope that you got my point.

Thanks,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=497490

Hi

Try this

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
Rows(ActiveCell.Row).Select
Selection.Cut
ActiveSheet.Next.Select
ActiveSheet.UsedRange.SpecialCells(xlLastCell).Sel ect
Row = ActiveCell.Row + 1
Cells(Row, 1).Select
ActiveSheet.Paste
Cells(Row, 1).Select
Application.CutCopyMode = False
ActiveSheet.Previous.Select
'Selection.Delete Shift:=xlUp
'ActiveCell.Select
End Sub

Thanks
Karthik Bhat