PDA

View Full Version : Solved: cut and pasted into Sheet2



marreco
10-12-2012, 05:33 PM
Hi.
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

jolivanes
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
'Or
'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.

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

thank you!!