Cut and paste vba code

[caos88]

Hello everyone,

i have datea to be copied and paste to another sheet according to the value. However, i made the code but it will overwrite the old data if i continue on another sheet. How to avoid it ?

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


' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("sheet1")
Set Target = ActiveWorkbook.Worksheets("sheet2")

j = 1 ' Start copying to row 1 in target sheet
For Each c In Source.Range("I1:I1000") ' Do 1000 rows
If c = "Closed" Then
Source.Rows(c.Row).Copy Target.Rows(j)
Source.Rows(c.Row).Cut Target.Rows(j)
j = j + 1

End If
Next c
End Sub

 

[Fluff]

Are you saying that you want the data on sheet cleared, before running the macro?
If not, then my code should work as it doesn't overwrite anything, just adds it below the last lot of data

 

[DataBlake]

alternatively using your code you provided just change j to identify the last row
see if this works (i havent tested it)

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


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("sheet1")
    Set Target = ActiveWorkbook.Worksheets("sheet2")

    j = Target.Range("A" & Rows.Count).End(xlUp).Row + 1     ' Start copying 1 down from the last row on sheet
    For Each c In Source.Range("I1:I1000")   ' Do 1000 rows
        If c = "Closed" Then
           Source.Rows(c.Row).Cut Target.Rows(j)
           j = j + 1
     
        End If
    Next c
End Sub

 

[caos88]

Thank you @DataBlake this is working very well.
Thanks @Fluff too !!

 

[Fluff]

Glad we could help & thanks for the feedback