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]

Hi & welcome to MrExcel.
Is this what you want?

Sub caos()
   With Sheets("Sheet1")
      .Range("A1:I1").AutoFilter 9, "Closed"
      With .AutoFilter.Range.Offset(1).EntireRow
         .Copy Sheets("Sheet2").Range("I" & Rows.Count).End(xlUp).Offset(1, -8)
         .Delete
      End With
      .AutoFilterMode = False
   End With
End Sub

 

[caos88]

Hi,

thank you. not really. This code will remove the rows only in A1:I1. I would like to cut and copy rows with a certain value (closed) to another sheet. and i don't want to overwrite items on the destination sheet.

 

[MARK858]

Have you actually tested the code that Fluff has posted?

 

[Fluff]

This code will remove the rows only in A1:I1

No it won't, in-fact it will never move that row.
Give it a go, on a copy of your workbook

 

[caos88]

yes, you are aright. it works but, i actually have to move only the rows when "completed" is triggered in the last column I and cut an paste to the other sheet......doing it on different sheet avoiding overwriting at the end

 

[caos88]

The code that i posted earlier works as well......but it overwrites if i run it different times from different sheets.

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]

Do you want to run the code on the active sheet & move the rows to Sheet2?

 

[caos88]

Yes, i want to run it on sheet 1 and cut and copy to sheet 2 after i choose the word completed.....on column I

 

[caos88]

if i have 2 rows with completed value and i run the code it will be ok. If i do it twice, the second time will overwrite.