macro question

[VQCHEESE]

I have a macro that sorts a column, and copies and pastes data based on that sort to a new sheet within same workbook. But this task is repeated and when the macro copies it over writes the data from the first time the macro runs. below is the code from my macro the sheet i am copying from is Todo and the sheet i am copying to is completed. also i would need to be able to have the macro select a new set of data to be copied over based on the sorting as well.

Columns("A:H").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$39").AutoFilter Field:=5, Criteria1:="<>"
Range("A4:H7").Select
Selection.Cut
Sheets("Completed").Select
Range("A4").Select
ActiveSheet.Paste
Sheets("Todo").Select
ActiveSheet.Range("$A$1:$H$39").AutoFilter Field:=5

 

[pboltonchina]

Try this on a copy of your data as it is untested.

Columns("A:H").AutoFilter
ActiveSheet.Range("$A$1:$H$39").AutoFilter Field:=5, Criteria1:="<>"
Range("A4:H7").Cut Destination:=Sheets("Completed").Range.Cells(Rows.Count, "A").End(xlUp)
Sheets("Todo").Select
ActiveSheet.Range("$A$1:$H$39").AutoFilter Field:=5

 

[VQCHEESE]

ok i have alot more done on this but am still missing a big piece.

When i sort by the criteria(which is weahter there is an x in the cell. it will sort fine and only show which ones have the x. but the cells may not always be in order. right now i have an x in cell 4, 8, 9, 10, as you can see from below its only taking rows 8:10, how would i make that take 4 also. But then if a row 12 has data in it but no x i dont want that to copy, how would i take those rows out.




ActiveSheet.Range("$A$1:$H$39").AutoFilter Field:=5, Criteria1:="<>"
Rows("8:10").Select

 

[pboltonchina]

Do you want copy all rows that contain an x? Which column would the x be in? Which sheet is this data in, and which sheet do you want it to go to?

 

[VQCHEESE]

Do you want copy all rows that contain an x? Which column would the x be in? Which sheet is this data in, and which sheet do you want it to go to?

Yes all rows that have the x,
x is in column E
Todo is the sheet i am copying from
Completed is the sheet i am copying to.

 

[pboltonchina]

Try this

Dim LR As Long, i As Long
Sheets("Todo").Select
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("E" & i).Value = "x" Then Rows(i).Copy Destination:=Sheets("Completed").Range.Cells(Rows.Count, "A").End(xlUp)
Next i