Significantly more involved than expected due to merged cells, cells which aren't really empty (they contain conditional formatting and #N/A) and trying to make it robust.
Here's the beginnings of some trial code:
Sub blah()
Set Qty = ActiveSheet.Columns(2).Find(what:="Qty", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
If Qty Is Nothing Then
MsgBox "no cell in column B containing ""Qty"", exiting…"
Exit Sub
Else
Set myrng = Intersect(Qty.CurrentRegion.SpecialCells(xlCellTypeConstants, 1), Columns(2))
' myrng.Select
myrng.Areas(1).Resize(, 2).Copy
Application.Goto Sheets("stock_new").Cells(Rows.Count, "C").End(xlUp).Offset(1)
End If
End Sub
It currently starts by looking at the active sheet, looks for Qty in column B, it then takes the current region of that cell (what gets selected when on the spreadsheet you select that cell, press F5 on the keyboard, click Special…, and choose Current Region, and click OK), it then takes the plain numeric constants (not formulae resulting in numbers (what you get when you press F5 as before and choose Constants and tick only the Numbers checkbox), then it takes only column B cells of all that, which has (or not) multiple non-contiguous ranges, and takes only the first of these, widens it to 2 columns, copies that into the clipboard. Then it jumps to the expected destination.
Were you to press Enter on the keyboard at this stage the data will be copied to the selected cell. If instead, you switch back to the invoice sheet you'll see the shimmering outline indicating whch cells have been copied to the clipboard.
So the types of further questions which arise are:
- Will there ever be any gaps in the invoice list (easy to imagine there could be, either for aesthetic purposes or because an extra item or two was entered by mistake and you've deleted them).
- I'd readily guess you don't want the discount line to be copied - but will/could that discount line(s?) appear elsewhere in the list?
- Can the list be longer than it is now (would you make room for more items by inserting rows into an invoice which needs it)?
- Are there going to be multiple invoices that you will be working on (so the sheet may not always be called invoice)?
- Which workbook are you putting the code into?
I can handle all of these but may not need to! (I could, for example, just start by using a hard-coded range: Sheets("invoice").range("B15:C28")
or ("B15:C29")??
Am I right in guessing that you have other code in the real workbooks?
Anyway, a start.