View Full Version : Solved: cut and pasted into Sheet2

10-12-2012, 05:33 PM
In cell 'C4' is entered a number to locate the data in a table.

I would like to clicak the button 'Post', and the code will select the line in the table according to the criteria and cut out that line putting on Sheet2

10-12-2012, 07:51 PM
Sub TryThis()
Dim LR As Long
Dim c As Range
LR = Cells(Rows.Count, 3).End(xlUp).Row
With Range("C7:C" & LR)
Set c = .Find(Range("C4").Value, LookIn:=xlValues)
Range(c, c.Offset(, 2)).Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, 1).End(xlUp).Offset(1, 0)
Range(c, c.Offset(, 3)).ClearContents
'Range(c, c.Offset(, 3)).Delete Shift:=xlUp
'The above line will wreak havoc with formulas in cells D4 and E4
End With
End Sub

Note: If you delete the cells and shift up, the formulas in cells D4 and E4 will be wrongly referenced.

10-13-2012, 05:49 PM
It was great!

thank you!!