Copy data from range if there is data plus other static data if more than one row

[wolthers]

Hi there, sorry if topic is a bit confusing, hard to describe what I need in one topic.

I am making a automatic database sheet for all the sales my team and I make.
A sale can be for one shipment month, or for several shipment months, and volume can vary as well, so the data that will be copied is not always the same range, but some data will always be the same.

Data that will always be the same:
Buyer, D5
Seller, I5
Quality: D14
Price: D15
Commission S: D9
Commission B: I9

Data that can be from a range depending if there is data:
Shipment month range(B19:B32)
Volume range(E19:E32)
Our reference (I19:I32)

Above will be copied and pasted into another sheet called Historico (history) as follows:

A	B*	C	D	E	F	G	H	I	J
Date(today)	Contract nr. (I19:I32)	Buyer (D5)	Seller (I5)	Quality (D14)	Price (D15)	Shipments (B19:B32)	Volume (E19:E32)	Commission Seller (D9)	Commission Buyer (I9)

<tbody>
</tbody>

Column B: (is our reference number, which is the last number +1 and there's a macro for that too shown in the bottom)


I have a macro that copies cells from the range, but will also copy blank cells - and it won't fill up with the data that should be the same, for instance, if I fill in 4 rows of shipments, say Jan through April and their respective contract numbers and volume, but also fill in the same data of buyer, seller, quality etc.

Dim ms As Worksheet, NRSet ms = Sheets("historico")
  With Sheets("Sale")
 


      NR = ms.Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1




  If ms.Range("B" & NR - 1) = (Range("I19", "I32").Value) Then  ' Contract Nr. need to get a way to see if there is a row with data, copy the data, but I don't know how to do it.
      NR = ms.Cells.Find("*", , , , xlByRows, xlPrevious).Row
  End If


      ms.Range("A" & NR) = Date
       
          
      .Range("I19", "I32").Copy
       ms.Range("B" & NR).PasteSpecial xlValues, Transpose:=True   'Contract nr
       
      .Range("D5").Copy
       ms.Range("C" & NR).PasteSpecial xlValues, Transpose:=True   'Buyer
       
      .Range("I5").Copy
      ms.Range("D" & NR).PasteSpecial xlValues, Transpose:=True   'Seller
       
       .Range("D14").Copy
       ms.Range("E" & NR).PasteSpecial xlValues, Transpose:=True   'Quality
       
       .Range("D15").Copy
       ms.Range("F" & NR).PasteSpecial xlValues, Transpose:=True   'Price
       
        .Range("B19", "B32").Copy
       ms.Range("G" & NR).PasteSpecial xlValues, Transpose:=True   'Shipments need to get a way to see if there is a row with data, copy the data, but I don't know how to do it.
       
        .Range("E19", "E32").Copy
       ms.Range("H" & NR).PasteSpecial xlValues, Transpose:=True    'Quantity need to get a way to see if there is a row with data, copy the data, but I don't know how to do it.
       
        .Range("D9").Copy
       ms.Range("I" & NR).PasteSpecial xlValues, Transpose:=True   'Commission Seller
       
        .Range("I9").Copy
       ms.Range("J" & NR).PasteSpecial xlValues, Transpose:=True   'Commission buyer
      
       
     Application.CutCopyMode = False

End With

In case the contract number is the same, it will just refresh the data next to it, so it won't add new contracts which is why there is the macro to only add new row if the number is new.
To get new number, I made a macro that clears the old shipment months and volume, and also gets the value of the last contract number and places it on a cell (L5), the contract numbers on row I19:I32 will get the number based of that cell number.

Dim FinalRow As LongSet ws = ActiveWorkbook.Sheets("Historico")
Set ws2 = Worksheets("Sale") 'Change this to the name of the new worksheet you want the data pasted to




FinalRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
    ws.Range("B" & FinalRow).Copy
       ws2.Range("L5").PasteSpecial xlPasteValuesAndNumberFormats
       
      Range("B19", "E32").ClearContents

I made two scenarios and posted pictures in case there are any questions of what I really need:

1) a sale with only one month of shipment
2) a sale with more than one month of shipment
3) the result after running the macro on the database sheet
4) what the result should actually be after running the macro


Scenarios 1:

Scenario 2:

Failed Outcome:

It should be:

Sorry for the very long post, but just wanted to make sure it would be understood, if there still are any questions, please let me know