VBA for extract data from invoice

Hi Ramzein (see revision note below)

In your first line of code, you're trying to find the next free row number in "Invoice Inventory" (based on column 9) but you select select the entire row (which isn't necessary) so you don't get ERow. 

In the attached revised workbook, you'll see I've set Erow as an integer then used this modification of your statement will get the row number you need for Erow (but based on column 1 contents)

ERow = Sheet11.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Then values from the Invoice (sheet13) can be imported using statements like:

Sheet11.Cells(ERow, 1).Value = Sheet13.Cells(22, 8).Value

In the full code below, you'll see I've extracted some cells (but not all) within a With.. End With (to save repeating "Sheet11"). Note that for cells E#, F# and G# I've extracted the data from the data now imported in cell D#.

If you run the code, it will keep repeating data from the Invoice sheet in new rows (though in practice I guess you will only run the macro once you've created a new invoice) .

REVISION: in the revised file attached (following your comments) I've defined a new variable (ItemCount As Integer) and used that to extract item data ALONG WITH the common invoice data. This means you could filter on the Inventory to see all sales of a particular item say).

There's now a For/ Next loop where (if the ItemCode on the invoice isn't blank), the common data is copied along with the column data you indicated from that row number on the Invoice. In that loop, ERow is increased so a new row could be written next time.

If the ItemCode hasn't been completed, it skips to Finish where the user is told how many rows were copied. This means if there are only 3 items on the Invoice (I've added  imaginary data), the Esle statement on the fourth loop causes it to exit the sub so only 3 rows are copied to the Inventory Invoice sheet.

Please try.

Given your Invoice is probably a fixed template, you might use Name Manager (and Names in the code) to make the code more succinct and read better.

Hope this helps. If so, please Select this answer.

Sub AddMain()

Dim ERow As Integer, ItemCount As Integer

ERow = Sheet11.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ' changed to column 1 not 9 i.e if there's an invoice number

With Sheet11 ' in Invoice Inventory sheet...

For ItemCount = 34 To 43

    If Sheet13.Cells(ItemCount, 4) <> "" Then ' used Item Code since No contains a formula
    'add the common data
        .Cells(ERow, 1).Value = Sheet13.Cells(22, 8).Value 'copy value from relevant cell on logical sheet 1 "Invoice"
        .Cells(ERow, 2).Value = Sheet13.Cells(20, 5).Value ' etc.
        .Cells(ERow, 3).Value = Sheet13.Cells(20, 8).Value
        .Cells(ERow, 4).Value = Sheet13.Cells(21, 8).Value
            .Cells(ERow, 5).Value = Day(.Cells(ERow, 4)) ' Get day from date in D
            .Cells(ERow, 6).Value = MonthName(Month(.Cells(ERow, 4))) ' get the month name from month number of date in D
            .Cells(ERow, 7).Value = Year(.Cells(ERow, 4)) ' get the year from date in D
        .Cells(ERow, 8).Value = Sheet13.Cells(21, 5).Value
     'add the item data
        .Cells(ERow, 9).Value = Sheet13.Cells(ItemCount, 3).Value
        .Cells(ERow, 10).Value = Sheet13.Cells(ItemCount, 5).Value
        .Cells(ERow, 11).Value = Sheet13.Cells(ItemCount, 7).Value
        .Cells(ERow, 12).Value = Sheet13.Cells(ItemCount, 9).Value
    
     ERow = ERow + 1 'increase sheet 11 row counter
    
    Else
        GoTo Finish
    
    End If
    
Next ItemCount

End With

Finish:

MsgBox "Copied invoice details with " & ItemCount - 34 & " item rows"

End Sub