Cut from table row based on cryteria to diferent table

[arn0ldas]

Hello i need HELP
have 2 - TABLES
1 - TABLE NAME (ORDERS)
COLUMNS:

• STATUS
• QTY
• DATE
• NAME

1 - TABLE NAME (ARCHIVE)
COLUMNS:

• STATUS
• QTY
• DATE
• NAME
• COUNTRY
• PRICE

I need cut all rows from table (ORDERS) and insert in table (ARCHIVE) when in colum (status) value is "CLOSED"

THNX ALL

 

[MandeepBaluja]

Query incomplete Do you want a macro solution or you can work with filters to select the data you wish for Example here you wrote status is closed apply filter copy it and then paste in archive table, OR you wish to do something different

 

[arn0ldas]

Query incomplete Do you want a macro solution or you can work with filters to select the data you wish for Example here you wrote status is closed apply filter copy it and then paste in archive table, OR you wish to do something different

i m loking for macro solution becose its jest sample i will use this for elot of different table after

 

[MandeepBaluja]

Sub CopyTable_Rows_to_other_Table()


ActiveSheet.ListObjects("Orders").Range.AutoFilter Field:=6, Criteria1:= _
"Closed"
ActiveSheet.ListObjects("Orders").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Copy


With ActiveSheet.ListObjects("ARCHIVE")
.ListRows(.ListRows.Count).Range.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll
End With


End Sub

 

[arn0ldas]

Sub CopyTable_Rows_to_other_Table()


ActiveSheet.ListObjects("Orders").Range.AutoFilter Field:=6, Criteria1:= _
"Closed"
ActiveSheet.ListObjects("Orders").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Copy


With ActiveSheet.ListObjects("ARCHIVE")
.ListRows(.ListRows.Count).Range.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll
End With


End Sub

i need CUT row not copy i try change tu cut but dont works :/
or jest need dellet selected after ?

 

[MandeepBaluja]

if your second table is on another sheet you can simply delete rows like this
Range("A2:A10").SpecialCells(xlCellTypeVisible).Rows.SelectSelection.Delete

Or try this to clear the contents if other table is in adjacent to first table


Sub CopyTable_Rows_to_other_Table()


ActiveSheet.ListObjects("Orders").Range.AutoFilter Field:=6, Criteria1:= _
"Closed"
ActiveSheet.ListObjects("Orders").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Copy


With ActiveSheet.ListObjects("ARCHIVE")
.ListRows(.ListRows.Count).Range.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll
End With

ActiveSheet.ListObjects("Orders").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.clearcontents

End Sub

 

[arn0ldas]

if your second table is on another sheet you can simply delete rows like this
Range("A2:A10").SpecialCells(xlCellTypeVisible).Rows.SelectSelection.Delete

Or try this to clear the contents if other table is in adjacent to first table


Sub CopyTable_Rows_to_other_Table()


ActiveSheet.ListObjects("Orders").Range.AutoFilter Field:=6, Criteria1:= _
"Closed"
ActiveSheet.ListObjects("Orders").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Copy


With ActiveSheet.ListObjects("ARCHIVE")
.ListRows(.ListRows.Count).Range.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll
End With

ActiveSheet.ListObjects("Orders").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.clearcontents

End Sub

ohh its works perfect jest one moment when insert data in table "ARCHIVE" insert one emty row below hou i can fix this ?