Move data from one sheet to another based on value

[JJabra]

Hi Guys,

I want a code that moves a whole row from one tab to another when there is the word done in row L. This would run on the press of a button.

I have the below code, just not sure exactly how to adapt it to delete the row

Sub CopyDone()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    
    Set Source = ActiveWorkbook.Worksheets("Main Tab")
    Set Target = ActiveWorkbook.Worksheets("Done")

    j = 2
    For Each c In Source.Range("L2:L1000")
        If c = "Done" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

Any help would be greatly appreciated. Thanks in advance!

 

[dmt32]

Hi,
try

Sub CopyDone()
    Dim Cell As Range, CopyRange As Range
    Dim Target As Range
    Dim Source As Worksheet
    
    Set Source = ActiveWorkbook.Worksheets("Main Tab")
    Set Target = ActiveWorkbook.Worksheets("Done").Range("A2")
    
    For Each Cell In Source.Range("L2:L1000")
        If UCase(Cell) = "DONE" Then
            If CopyRange Is Nothing Then
                Set CopyRange = Cell
            Else
                Set CopyRange = Union(CopyRange, Cell)
            End If
        End If
    Next Cell
    
    If Not CopyRange Is Nothing Then
        With CopyRange.EntireRow
        .Copy Target
        .Delete shift:=xlShiftUp
    End With
    End If
End Sub

Dave

 

[Fluff]

Another option

Sub CopyDone()
    Dim Source As Worksheet
    Dim Target As Worksheet

    
    Set Source = ActiveWorkbook.Worksheets("Main Tab")
    Set Target = ActiveWorkbook.Worksheets("Done")

    With Source
      .Range("L1:L1000").AutoFilter 1, "Done"
      .AutoFilter.Range.Offset(1).EntireRow.Copy Target.Range("A2")
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[JJabra]

Hi both thanks for your quick responses, When attempting to run the macros both came up with "Run-time error '1004' Application- defined or object-defined error" I'm not sure i have missed something?

 

[Fluff]

Which line of code gave the error?

 

[dmt32]

Hi both thanks for your quick responses, When attempting to run the macros both came up with "Run-time error '1004' Application- defined or object-defined error" I'm not sure i have missed something?

Are any of the sheets protected?

Dave

 

[JJabra]

It came up when i got to

.Range("L1:L1000").AutoFilter 1, "Done"
.AutoFilter.Range.Offset(1).EntireRow.Copy Target.Range("A2")
.AutoFilter.Range.Offset(1).EntireRow.Delete
.AutoFilterMode = False

 

[JJabra]

None of the sheets are protected

 

[Fluff]

Which of those lines were highlighted when you clicked Debug?

 

[dmt32]

None of the sheets are protected

where did it fail on my solution?

Dave