Moving rows from 1 excel worksheet to another

Microsoft Microsoft excel 2007 full vers...
November 5, 2010 at 03:14:50
Specs: Windows XP
I have produced a worksheet called 'Records' with a different customer record on each row. When a case is cleared I select 'Y' in column D. Is it possible to set up a macro that once activated moves all cleared cases to worksheet 2 called 'Cleared'. Ensuring it doesn't delete what is already on worksheet 'Cleared'.

See More: Moving rows from 1 excel worksheet to another

Report •

November 5, 2010 at 12:12:45
Right-Click the sheet tab for Records
Paste this code into the pane that opens
Enter a Y in Records, Column D

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
'Was change made to Column D?
  If Target.Column = 4 Then
'If yes, was a Y entered?
   If Target = "Y" Then
'If yes, Store Row number, Determine next empty Row in
'Cleared sheet, Move Row, Delete Row
    delRow = Target.Row
      nxtRow = Sheets("Cleared").Range("A" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Cut Destination:=Sheets("Cleared").Range("A" & nxtRow)
      Rows(delRow).EntireRow.Delete shift:=xlUp
   End If
  End If
 Application.EnableEvents = True
End Sub

Report •

November 9, 2010 at 12:31:25
excellent, really appreciate it, works a treat.


Report •

Related Solutions

Ask Question