Move Data from One Sheet to another

[uk747]

Trying to Move Data from Sheet 1 to Sheet2 if Column D has Closed in it
Below code doesnt seem to move it all. is there a better easier way to do it. ALso dont necessarily need selected cells prefer rage to be just Col D and when it finds it will copy all the rows to sheet 2 and then delete the Data on Sheet1

ALso need it to paste to next available row as will be moving stuff from sheet 1 to sheet 2 once a month

Thanks

Dim mycell
For Each mycell In Selection.Columns(4).Cells
  If mycell.Value = "Closed" Then
    mycell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
    mycell.EntireRow.Delete
  End If

 

[uk747]

bump

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Closed" in column D and press the RETURN key. Please note that this macro will copy one row at a time after you enter "Closed" in any cell in column D.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target.Value = "Closed" Then
        Target.EntireRow.Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[uk747]

Thanks
Is there an equivalent if not on the sheet. I.e. if I was on sheet2 and wanted to run code on sheet3

 

[mumps]

The macro is a Worksheet_Change event and it refers to the active sheet. This means that the macro will run automatically when a cell is changed. In your case it is limited to a change in column D and the row it copies must be on the same sheet. Is the column D that contains "Closed" on a different sheet from the sheet containing the data you want moved? If so, use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of the two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[uk747]

The rows copied and deleted will be on the same sheet

I.e. any value in column 4 on sheet1 which has closed in it, that row(s) will be transferred to next available row on sheet2 and then the rows on sheet1 that have been copied will be deleted

 

[mumps]

That’s what the macro I suggested does now.

 

[uk747]

But I won't be necessarily on sheet1. I wanted it to be more versatile for example if sheet 1 was hidden

 

[mumps]

Then you would need to run a macro manually. Will that work for you?

 

[uk747]

That's fine yes thanks. It only needs to be run once a week and not every time something in 4th column changes