A macro to move or copy a row with a condition

[aysam]

Hi All
I need a macro to move or copy any row that has the word (( moved )) to another sheet with the same name.
the file has thousands of rows.
thanks in advance

 

[montecarlo2012]

Is fine, I just want to help you, in case you need help next time, would be easier if you follow my lines.
ok, I think I got your problem solve on column F you have the word moved, so my code will get that from sheet 1 will copy on sheet 2 and then delete from 1
enjoy.

Sub montecarlo()

    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

   
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")

    j = 1
    For Each c In Source.Range("F1:F1000")
        If c = "moved" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
       Range("F:F").Delete
            
End Sub

As an example I write on sheet 1 like this

after I run my code on sheet 2 you will get this

then sheet 1 column F will be delete.

 

[mumps]

Try this macro. It assumes that you have headers in row 1 and your data starts in row2 and that there are no blank rows in your data. Change the sheet name that contains your data (in red) to suit your needs.

Sub MoveRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Moved")
    With srcWS
        .Cells(1, 1).CurrentRegion.AutoFilter 6, "moved"
        .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[aysam]

Thanks very much
It works well

 

[mumps]

You are very welcome.

 

[aysam]

Sorry
one more edit needed
this line of the code deletes the entire row

.AutoFilter.Range.Offset(1).EntireRow.Delete

Is it possible to re-edit it to delete the range (A: L) and shift the cells up ????
I hope so

 

[mumps]

Try replacing that line of code with this one:

.Range("A2:L" & LastRow).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp

 

[aysam]

Unfortunately
It still deletes the entire row

 

[montecarlo2012]

when there is a new use of the code,it shouldn't delete the old information in the destination sheet
then

 

[aysam]

I mean it deletes the entire row in sheet 1.What I want is to delete the range. A.L. in sheet 1

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. 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. (de-sensitized if necessary).