Append rows from 1 wb to bottom of another wb based on column values

[Jpeep]

I've used this site many times for tips so thank you, but never posted a direct question. I'm an excel hack and I can do this task with a few 'hacky' clicks but I now have to put it into a macro for an 'end user'.

2 open workbooks: Daily Despatch Report & Ready to Invoice
I need to copy all the rows in worksheet 'invoicing' in wb Daily Despatch Report that have 'Carton' in column Data Level and 'Shipped Invoice' in column Order Status and append them to the bottom of worksheet 'ready to invoice' in wb 'Ready to Invoice'
It should be easy and I'm sure it is for you people but...

 

[Jpeep]

So, my apologies for not adding some data to play with...
My problem is I get stuck on ranges and do whiles. I understand the concepts and I've tried to adapt code from different threads here but I just cant seem to get it right.
[FONT=&quot]wb1: Name: Standard_Despatch_Report W/sheet name: invoicing[/FONT]

[FONT=&quot]Customer Order Number [/FONT]	[FONT=&quot]Data Level [/FONT]	[FONT=&quot]Man Ref Number [/FONT]	[FONT=&quot]inv status [/FONT]	[FONT=&quot]del status [/FONT]	[FONT=&quot]status date [/FONT]	[FONT=&quot]Order Status [/FONT]	[FONT=&quot]Modified Date Time[/FONT]
ORD091373	Header	1040825978				Shipped Invoice	3/05/2018 10:09
ORD091373	Line	1040825978				Shipped Invoice	3/05/2018 10:09
ORD091373	Line Instruction	1040825978				Shipped Invoice	-
ORD091373	Carton	1040825978				Shipped Invoice	3/05/2018 10:09
ORD091374	Header	1040825979				Shipped Invoice	3/05/2018 10:09
ORD091374	Header Instruction	1040825979				Shipped Invoice	-
ORD091374	Header Instruction	1040825979				Shipped Invoice	-
ORD091374	Line	1040825979				Shipped Invoice	3/05/2018 10:09
ORD091374	Line Instruction	1040825979				Shipped Invoice	-
ORD091374	Carton	1040825979				Shipped Invoice	3/05/2018 10:09

<tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>

wb2 Name: Ready to invoice w/sheet name: ready to invoice

[FONT=&quot]Customer Order Number [/FONT]	[FONT=&quot]Data Level [/FONT]	[FONT=&quot]Ref number [/FONT]	[FONT=&quot]AP status [/FONT]	[FONT=&quot]Delivery status [/FONT]	[FONT=&quot]del status date [/FONT]	[FONT=&quot]Order Status [/FONT]	[FONT=&quot]Modified Date Time[/FONT]
ORD091390	Carton	1040842792		Not found	Not found	Shipped Invoice	3/05/2018 14:33
ORD091390	Carton	1040842792		Not found	Not found	Shipped Invoice	3/05/2018 14:33
ORD091391	Carton	1040842793		Delivered	3/6/2018 11:45 AM	Shipped Invoice	3/05/2018 13:57
ORD091392	Carton	1040842794		Not found	Not found	Shipped Invoice	3/05/2018 13:57
ORD091393	Carton	1040842795		Delivered	3/6/2018 2:06 PM	Shipped Invoice	3/05/2018 14:51
ORD091394	Carton	1040842796		In Transit	3/5/2018 8:22 PM	Shipped Invoice	3/05/2018 13:57
ORD091394	Carton	1040842796		In Transit	3/5/2018 8:22 PM	Shipped Invoice	3/05/2018 13:57

<tbody>
</tbody>

 

[Jpeep]

OK so I'm answering my own question in case anyone else might find it useful. There is probably a better way of doing it but this works! Except for the bit where I have to close the source file without saving it. - Any ideas?

' add_new_orders to 'ready to invoice workbook' Macro
'
'Start by defining source and target wookbooks
Dim src As Excel.Workbook
Dim trg As Excel.Workbook
Dim src_wks As Excel.Worksheet
Dim trg_wks As Excel.Worksheet


'Now name them
Set src = Excel.Workbooks("RP1054_Standard_Despatch_Report_v3_GAMA_A39_Daily_View.xlsx")
Set trg = Excel.Workbooks("Ready to Invoice.xlsx")


'Now name the worksheets
Set src_wks = src.Worksheets("invoicing")
Set trg_wks = trg.Worksheets("ready to invoice")


'make excel know that the last row is not the first?
Dim lRow As Long


'Run the macro from within "invoicing"


'Let's start at row 2. Row 1 has headers
x = 2
'Start the loop
Do While Cells(x, 1) <> ""
'Look for data with 'Carton'
If Cells(x, 2) = "Carton " Then
'copy the row if it contains 'Carton'
Worksheets("invoicing").Rows(x).Copy
'Go to Ready to invoice. Activate it so I can put the data here
trg.Activate
Worksheets("ready to invoice").Activate
'Find the first empty row in ready to invoice
lRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Paste the data here
ActiveSheet.Paste Destination:=Worksheets("ready to invoice").Rows(lRow)
End If
'go to invoicing again and actvate it
src.Activate
Worksheets("invoicing").Activate
'Loop through the other rows with data
x = x + 1
Loop


'now close the despatch without saving it - hmmm dont know how to do this yet!


'
End Sub