VBA copy data to Copy Data To New Workbook Based On Criteria

I have 2 workbooks - Data file (Sheet: Andhra Pradesh) & Invoice file (new invoice to be added after the last invoice).

I want to use the pivot table in data workbook to create invoice in the invoice workbook. 1 PO number per 1 Invoice.

For example: row 9B DO3566521 will be copied into a new Invoice 300开发者_JAVA百科 in Invoice workbook with the following information:

• Order Closed Date (A9) to be copied into I16
• PO Number DO666 (B9) to be copied into B31,
• Order ID 1234 (C9) to be copied into A34,
• Spec Name - 3 items (E9:E11) to be copied into B34:B36,
• Invoice Qty (F9:F11) to be copied into G34:G36,

• Invoice Amount (G9:G11) to be copied into I34:36

• PO Number DO667 (B12) should be copied into another new Invoice 301 (a new worksheet 301 to be added in Invoice workbook)...

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!!

BTW, I also have a macro (Sub Get_Spelling()) 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.

Data workbook - Sheet Name: Andhra Pradesh

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 

Invoice workbook - Sheet name: Invoice Number (e.g Inv 300)

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

Here is my 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

I'm kind of confused by some of what you're asking but something along the lines of the following should work. Just adapt it to your needs.

Sub Main()
Dim wkbook As Workbook
Set wkbook = Workbooks.Add

If Len(dir( ***Your Directory Path *****, vbDirectory)) = 0 Then
    MkDir ***Your Directory Path *****
End If

With wkbook
    .Title = "Workbook Title"
    .Subject = "Workbook Subject"
    .SaveAs FileName:= ***Your Directory Path *****\"Workbook Title.xls"
End With

With wkbook.Worksheets("Sheet1")
.Range("I16") = **Your Original Worksheet**.Worksheets("Sheet1").Range("A1").value
'repeat for all values
end with

wkbook.save
wkbook.close
end sub

If I were you I'd pass your values in the original workbook to a variant array and then read through them with a for loop. As in..

dim myValues as Variant
dim I as Integer

myValues = Worksheets("Sheet1").Range("A9:G11").Value

for I = LBound(myValues) to Ubound(myvalues)
'do work here (See the with wkbook statement above)
next I

That should get you started.