StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVBA copy data to Copy Data To New Workbook Based On Criteria
text
Body
copied!<p>I have 2 workbooks - Data file (Sheet: Andhra Pradesh) & Invoice file (new invoice to be added after the last invoice).</p> <p>I want to use the pivot table in data workbook to create invoice in the invoice workbook. 1 PO number per 1 Invoice.</p> <p>For example: row 9B DO3566521 will be copied into a new Invoice 300 in Invoice workbook with the following information:</p> <ul> <li>Order Closed Date (A9) to be copied into I16</li> <li>PO Number DO666 (B9) to be copied into B31,</li> <li>Order ID 1234 (C9) to be copied into A34, </li> <li>Spec Name - 3 items (E9:E11) to be copied into B34:B36, </li> <li>Invoice Qty (F9:F11) to be copied into G34:G36, </li> <li><p>Invoice Amount (G9:G11) to be copied into I34:36</p></li> <li><p>PO Number DO667 (B12) should be copied into another new Invoice 301 (a new worksheet 301 to be added in Invoice workbook)...</p></li> </ul> <p>I can get the Invoice Workbook to add/copy a new worksheet but I'm having trouble copying over the data & writing the loop function. how to create a new invoice if it's a different PO? Any help would be much appreciated!!</p> <p>BTW, I also have a macro (<code>Sub Get_Spelling()</code>) to convert numbers into words in invoice worksheet (A55). Just wondering how can I join them together so it gets updated automatically when invoice is created.</p> <p>Data workbook - Sheet Name: Andhra Pradesh</p> <pre><code>A9 B9 C9 D9 E9 F9 G9 Order Closed Date PO Number Order ID Item Index Spec Name Qty Invoice Amt 15/09/11 DO666 1234 1 A 10 $100 2 B 20 $200 3 C 30 $300 DO667 567 1 L 40 $100 2 K 50 $200 </code></pre> <p>Invoice workbook - Sheet name: Invoice Number (e.g Inv 300)</p> <pre><code>B31 PO Number DO666 A34 B34 G34 I34 Order ID Spec Name Qty Invoice Amt 1234 A 10 $100 B 20 $200 C 30 $300 Invoice 301 B31 PO Number: DO667 </code></pre> <p>Here is my code: </p> <pre><code>Option Explicit Sub Create_Invoice() Dim oldsheet As Worksheet Dim newSheet As Worksheet Dim oldnumber As Integer Dim newnumber As Integer Dim databook As Workbook Dim datasheet As Worksheet Dim invbook As Workbook Set databook = ActiveWorkbook Set invbook = ActiveWorkbook Application.Workbooks.Open ("C:\Users\Owner\Desktop\New folder\AP VAT Inv 201 -.xls") 'Set invbook = ActiveWorkbook oldnumber = ActiveSheet.Name newnumber = oldnumber + 1 ActiveSheet.Copy After:=ActiveWorkbook.ActiveSheet Set newSheet = ActiveSheet ActiveSheet.Name = newnumber ActiveSheet.Range("I15").Value = newnumber ActiveSheet.Range("I16") = databook.Sheets("Andhra Pradesh").Range("A9") MsgBox "Invoices have been created successfully" ActiveWorkbook.Save End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload