MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to cut row from one worksheet/paste in another


Posted by Dwight on October 29, 2001 4:45 AM

Spreadsheet named "Ilinois"
Several hundred rows in a worksheet therein also named "Illinois" contain text or numbers in columns A:M. Several columns hidden.
I need to transfer the data for numerous of these rows to another worksheet (named "Deleted Prospects") within the same spreadsheet. Ideally, I would like to be able to click on any cell in the row to be transferred and then activate a macro which would select that row (or columns A:M in the row), cut the data and paste it into the last row in "Deleted Prospects" which doesn't already contain some data, and then delete the row from which the data had been cut, leaving the cursor in the next row below it.
Any help with such a macro will be greatly apreciated.
Dwight


Posted by Tom Urtis on October 29, 2001 7:09 AM

Is this what you want?

Dwight,

Try using this macro:


Sub PasteRowDelete()
' Macro recorded 10/29/2001 by Thomas Urtis
Application.ScreenUpdating = False
Worksheets("Illinois").Select
ActiveCell.EntireRow.Select
Selection.Cut
Sheets("Deleted Prospects").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Illinois").Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
Application.ScreenUpdating = True
End Sub


Tom Urtis

Posted by Dwight on October 29, 2001 7:58 AM

Just what I was looking for. Thanks, Tom NT