Moving rows

    Hi board,


    When I enter data into a certain column, I would like the row that the data is entered into to be automatically moved to another sheet. Is this easy to do? Ta.

    This code (when pasted into sheet VB) will copy a row when data is changed in column 5 ("E") and paste it in Sheet3, position A1:


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Column = 5 Then
    ActiveCell.EntireRow.Copy Sheets("Sheet3").Range("A1")
    End If
    End Sub


    Joel

    Thanks for that, it works as it should but it's not quite what I wanted. What I have is a list of items in a cupboard and when I remove an item I put a date in a column next to that item. That item is moved to another sheet so that I have 2 lists. I want to be able to do that automatically.


    Does that make sense? :duh:

    Sorry, don't have example to hand.


    What I have is a spreadsheet which is an inventory for stock parts we house in a cupboard. The column headings are - S/N, Item, Supplier and Date. The date is only filled in when the item is taken from the cupboard. I then want that item to be listed on a seperate sheet. At the moment I am doing it manually by cutting and pasting the entire row and I want to be able to do it automatically when the date is filled in.

    try this instead:


    Private Sub Worksheet_Change(ByVal Target As Range)


    Dim r1 As Long


    If Sheets("Sheet3").Range("A1") <> "" And Sheets("Sheet3").Range("a2") = "" Then
    r1 = 1
    Else
    r1 = Sheets("Sheet3").Cells(1, 1).End(xlDown).Row
    If Sheets("Sheet3").Range("A1") = "" Then r1 = 0
    End If
    If ActiveCell.Column = 4 And ActiveCell.Row &gt; 1 And ActiveCell.Value <> 0 Then
    r1 = r1 + 1
    ActiveCell.EntireRow.Copy Sheets("Sheet3").Cells(r1, 1)


    End If
    End Sub



    I assume your date is in column "D", other wise change activecell.column number accordingly.

    It may not have worked exactly what
    the filer expected, but this is very helpful to some problem that I had.


    Thanx for this.


    There is one problem with this code.


    When i press enter after entering date
    in column D, the code searches for
    Active Cell, which is below the one which
    i just entered.
    E.G. If i have entered Date in D4 and
    pressed enter, the cell goes to D5.
    At this time the chageselection event is triggered and the active cell D5, if having
    date filled in, that row is copied.


    Is there any solution?


    Thanx in Advance
    :o2

    Thanks: ~Yogendra

    Sounds like you have "Move selection after Enter" checked in Tools/Options/Edit. I have mine unchecked. If you want to keep it checked, modify my code to trigger from activecell minus one row!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login