Copy invoice data to another sheet in my workbook using a script

[jdbrown]

I have an invoice called (Invoice). I would like a script to get the invoice number, customer number and each line item, including Quantity and Amount for each item. I would like to get this information into another sheet in my workbook called (Invoice List 2).I have found some scripts online but the I have not been able to get them to function properly, mostly due to my lack of scripting knowledge of some of the funtions. The Customer field and Product field are using Data Validation, the Amount field is using a formula that is Price (not shown) x Quantity. Any help would be greatly appreciated thank you.
Invoice

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]

<strike></strike>Invoice#​

<strike></strike>[/TD]
[TD="align: center"]<strike></strike>

Customer​

<strike></strike>[/TD]
[TD="align: center"]<strike></strike>

Product​

<strike></strike>[/TD]
[TD="align: center"]<strike></strike>

Quantity​

<strike></strike>[/TD]
[TD="align: center"]<strike></strike>

Amount​

<strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]

John Doe​

<strike></strike>[/TD]
[TD="align: center"]<strike></strike>

ABC Book 1​

<strike></strike>[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]

$2.00​

<strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]<strike></strike>[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]<strike></strike>

ABC Book 2​

[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]

$4.00​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]<strike></strike>[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]<strike></strike>

ABC Book 3​

[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]

$2.00​

<strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Jane Doe[/TD]
[TD="align: center"]

ABC Book 1​

<strike></strike>[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]

$4.00​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]<strike></strike>[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

ABC Book 2​

[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]

$6.00​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Ed Doe[/TD]
[TD="align: center"]

ABC Book 1​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]

$2.00​

<strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]<strike></strike>[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

ABC Book 3​

[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]

$2.00​

<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]

<strike>







</strike>​

<strike>




</strike>This is how I would like the data to look in the work sheet called (Invoice List 2). I do not care if the Invoice# or Customer show up in every line for a particular invoice, but if it is easier to code by having these items show up in every line that would be fine.
​

Invoice List 2
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]

Invoice#​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]<strike></strike>

Customer​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]<strike></strike>

Product​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]<strike></strike>

Quantity​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]<strike></strike>

Amount​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]

John Doe​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]<strike></strike>

ABC Book 1​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]

$2.00​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]<strike></strike>[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

ABC Book 2​

[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]

$4.00​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]<strike></strike>[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

ABC Book 3​

[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]

$2.00​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

ABC Book 1​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]

$4.00​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]<strike></strike>[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

ABC Book 2​

[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]

$6.00​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

ABC Book 1​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]

$2.00​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]<strike></strike>[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

ABC Book 3​

[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]

$2.00​

<strike style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 0, 0); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 0, 0); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: none; border-top-width: 0px; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: center; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]

<strike></strike>





​

Script found online:
Sub SendToInvoiceList2()
Dim ws As Worksheet: Set ws = Worksheets("Invoice")
Dim wsData As Worksheet: Set wsData = Worksheets("Invoice List 2")
'declare and set the worksheets, amend as required
Dim i As Long, dataRows As Long
'TRANSFER data to Invoice List 2
I'm not sure what should go in the ws.Range below.
dataRows = ws.Range("B14").Columns(1).SpecialCells(xlCellTypeConstants, 1).Count
'count the number of Invoice lines with data (non-empty)

I'm not sure what should go in the ws.Range and the wsData.Range below.​

ws.Range("B1").Copy wsData.Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
'copy invoice lines to Invoice List 2
For i = 1 To dataRows 'loop from 1 to however many lines your named range "Invoice" has
ws.Range("E4").Copy
wsData.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("D6").Copy
wsData.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("B14").Copy
wsData.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("D14").Copy
wsData.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("E14").Copy
wsData.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


Next i
End Sub

<strike></strike>

 

[My Aswer Is This]

Looks like if we do this the simple way you want both sheets to be exactly the same.
Is that true?
If not point out the different.

 

[jdbrown]

Looks like if we do this the simple way you want both sheets to be exactly the same.
Is that true?
If not point out the different.

Yes you are correct I would like to send data from an Invoice to the Invoice List 2 and be able to increment the rows in Invoice List 2 as new Invoices are created.
Thanks

 

[My Aswer Is This]

Well that's a whole different situation.
Your saying every time you do something on one sheet you want a script to automatically enter the same data on another sheet.

So what exact action would cause the script to activate?


It would be difficult to write a script which would watch your one sheet for any activity and immediately perform that same action on a second sheet.

For example you enter 22 In Range("A1") on sheet(1) and then you immediately want 22 entered in Range("A1") on Sheet(2)

You delete Row(1) on sheet(1) and you want Row(1) deleted on sheet(2).

Now to write a script which says when you press a button Make a complete copy of Sheet(1) that would be easy.

 

[jdbrown]

Well that's a whole different situation.
Your saying every time you do something on one sheet you want a script to automatically enter the same data on another sheet.

So what exact action would cause the script to activate?


It would be difficult to write a script which would watch your one sheet for any activity and immediately perform that same action on a second sheet.

For example you enter 22 In Range("A1") on sheet(1) and then you immediately want 22 entered in Range("A1") on Sheet(2)

You delete Row(1) on sheet(1) and you want Row(1) deleted on sheet(2).

Now to write a script which says when you press a button Make a complete copy of Sheet(1) that would be easy.

I have a script for the invoice that when I click a button it runs the scripts listed below, I would like to use the same button to run the script to copy the data from the Invoice to Invoice List 2. I have some screenshots of my invoice and what I started out trying to do, but I don't see how to insert them into this reply. Thanks again.

Scripts
Save invoice as a PDF.
Increase invoice number by 1
Clear cells in invoice

Copy Invoice Number in cell E4 to Invoice List 2 column A starting in row 2.

Copy Invoice Customer name in Cell D6 to Invoice List 2 column B starting in row 2.

Copy Invoice Description Rows B14 thru B35 if populated to Invoice List 2 column C starting in row 2.

Copy invoice Quantity in rows D14 thru D35 if populated, to Invoice List 2 column D starting at row 2.

Copy Invoice Amount in rows E14 thru E35 if populated, to Invoice List 2 column E starting in row 2.


I found this script online and it works some what, if I have 1 item in my invoice and click the button to run the scripts it makes 2 identical rows with the same data in the Invoice List 2. Also, I am not sure what values to put in some rows in the script.

Script found online:

Sub SendToInvoiceList2()
Dim ws As Worksheet: Set ws = Worksheets("Invoice")
Dim wsData As Worksheet: Set wsData = Worksheets("Invoice List 2")
'declare and set the worksheets, amend as required
Dim i As Long, dataRows As Long

'TRANSFER data to Invoice List 2
​

I'm not sure what should go in the ws.Range below.​

dataRows = ws.Range("B14").Columns(1).SpecialCells(xlCellTypeConstants, 1).Count

'count the number of Invoice lines with data (non-empty)
I'm not sure what should go in the ws.Range and the wsData.Range below.
ws.Range("B1").Copy wsData.Range("F" & Rows.Count).End(xlUp).Offset(1, 0)

'copy invoice lines to Invoice List 2
For i = 1 To dataRows 'loop from 1 to however many lines your named range "Invoice" has
ws.Range("E4").Copy
wsData.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("D6").Copy
wsData.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("B14").Copy
wsData.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("D14").Copy
wsData.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("E14").Copy
wsData.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


Next i
End Sub

​

<strike style="background-color: transparent; color: rgb(0, 0, 0); font-family: Calibri; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>

​