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

J

Jpeep

New Member
Joined
Jan 31, 2018
Messages
3
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...
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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]
[TABLE="width: 699"]
<tbody>[TR]
[TD][FONT=&quot]Customer Order Number
[/FONT][/TD]
[TD][FONT=&quot]Data Level
[/FONT][/TD]
[TD][FONT=&quot]Man Ref Number
[/FONT][/TD]
[TD][FONT=&quot]inv status
[/FONT][/TD]
[TD][FONT=&quot]del status
[/FONT][/TD]
[TD][FONT=&quot]status date
[/FONT][/TD]
[TD][FONT=&quot]Order Status
[/FONT][/TD]
[TD][FONT=&quot]Modified Date Time[/FONT]
[/TD]
[/TR]
[TR]
[TD]ORD091373
[/TD]
[TD]Header
[/TD]
[TD]1040825978
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 10:09
[/TD]
[/TR]
[TR]
[TD]ORD091373
[/TD]
[TD]Line
[/TD]
[TD]1040825978
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 10:09
[/TD]
[/TR]
[TR]
[TD]ORD091373
[/TD]
[TD]Line Instruction
[/TD]
[TD]1040825978
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]ORD091373
[/TD]
[TD]Carton
[/TD]
[TD]1040825978
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 10:09
[/TD]
[/TR]
[TR]
[TD]ORD091374
[/TD]
[TD]Header
[/TD]
[TD]1040825979
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 10:09
[/TD]
[/TR]
[TR]
[TD]ORD091374
[/TD]
[TD]Header Instruction
[/TD]
[TD]1040825979
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]ORD091374
[/TD]
[TD]Header Instruction
[/TD]
[TD]1040825979
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]ORD091374
[/TD]
[TD]Line
[/TD]
[TD]1040825979
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 10:09
[/TD]
[/TR]
[TR]
[TD]ORD091374
[/TD]
[TD]Line Instruction
[/TD]
[TD]1040825979
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]ORD091374
[/TD]
[TD]Carton
[/TD]
[TD]1040825979
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 10:09
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 744"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]
wb2 Name: Ready to invoice w/sheet name: ready to invoice
[TABLE="width: 765"]
<tbody>[TR]
[TD][FONT=&quot]Customer Order Number
[/FONT][/TD]
[TD][FONT=&quot]Data Level
[/FONT][/TD]
[TD][FONT=&quot]Ref number
[/FONT][/TD]
[TD][FONT=&quot]AP status
[/FONT][/TD]
[TD][FONT=&quot]Delivery status
[/FONT][/TD]
[TD][FONT=&quot]del status date
[/FONT][/TD]
[TD][FONT=&quot]Order Status
[/FONT][/TD]
[TD][FONT=&quot]Modified Date Time[/FONT]
[/TD]
[/TR]
[TR]
[TD]ORD091390
[/TD]
[TD]Carton
[/TD]
[TD]1040842792
[/TD]
[TD][/TD]
[TD]Not found
[/TD]
[TD]Not found
[/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 14:33
[/TD]
[/TR]
[TR]
[TD]ORD091390
[/TD]
[TD]Carton
[/TD]
[TD]1040842792
[/TD]
[TD][/TD]
[TD]Not found
[/TD]
[TD]Not found
[/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 14:33
[/TD]
[/TR]
[TR]
[TD]ORD091391
[/TD]
[TD]Carton
[/TD]
[TD]1040842793
[/TD]
[TD][/TD]
[TD]Delivered
[/TD]
[TD]3/6/2018 11:45 AM
[/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 13:57
[/TD]
[/TR]
[TR]
[TD]ORD091392
[/TD]
[TD]Carton
[/TD]
[TD]1040842794
[/TD]
[TD][/TD]
[TD]Not found
[/TD]
[TD]Not found
[/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 13:57
[/TD]
[/TR]
[TR]
[TD]ORD091393
[/TD]
[TD]Carton
[/TD]
[TD]1040842795
[/TD]
[TD][/TD]
[TD]Delivered
[/TD]
[TD]3/6/2018 2:06 PM
[/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 14:51
[/TD]
[/TR]
[TR]
[TD]ORD091394
[/TD]
[TD]Carton
[/TD]
[TD]1040842796
[/TD]
[TD][/TD]
[TD]In Transit
[/TD]
[TD]3/5/2018 8:22 PM
[/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 13:57
[/TD]
[/TR]
[TR]
[TD]ORD091394
[/TD]
[TD]Carton
[/TD]
[TD]1040842796
[/TD]
[TD][/TD]
[TD]In Transit
[/TD]
[TD]3/5/2018 8:22 PM
[/TD]
[TD]Shipped Invoice
[/TD]
[TD]3/05/2018 13:57

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
228
sijpie
sijpie
K
  • Question Question
VBA for using a check box to Regen that row and then uncheck the box (ready to run macro again)
Replies
1
Views
679
Micron
M
M
  • Question Question
VBA Code to copy rows by date from one tab to another / repeat based on new date
Replies
3
Views
544
kevin9999
K
M
  • Question Question
VBA Code to Create Named Ranges for Entire Columns in a Worksheet Based on a List of Names in Another Worksheet
Replies
3
Views
1K
Norie
Norie
A
  • Question Question
Filter by Date macro
Replies
6
Views
644
kevin9999
K

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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
Back
Top