title: VBA copy data to Copy Data To New Workbook Based On Criteria
body: <p>I have 2 workbooks - Data file (Sheet: Andhra Pradesh) &amp; 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 &amp; 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>