Cut from table row based on cryteria to diferent table

arn0ldas

New Member
Joined
Feb 11, 2016
Messages
10
Hello i need HELP :eek:
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 :)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

MandeepBaluja

Board Regular
Joined
Jan 28, 2014
Messages
120
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

New Member
Joined
Feb 11, 2016
Messages
10
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

Board Regular
Joined
Jan 28, 2014
Messages
120
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

New Member
Joined
Feb 11, 2016
Messages
10

ADVERTISEMENT

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

Board Regular
Joined
Jan 28, 2014
Messages
120
if your second table is on another sheet you can simply delete rows like this
Range("A2:A10").SpecialCells(xlCellTypeVisible).Rows.Select
Selection.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

New Member
Joined
Feb 11, 2016
Messages
10
if your second table is on another sheet you can simply delete rows like this
Range("A2:A10").SpecialCells(xlCellTypeVisible).Rows.Select
Selection.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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,798
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top